i want to search coulmn a for an item and then transfer content from corresponding cell in column b to page 2 what is the formula to do this

Your looking for a =VLOOKUP() With your data like:

Sheet 1

A B 1) 123 XYZ 2) 456 ABC 3) 789 IOUOn Sheet 2 in cell A1 enter: 456

On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!A1:B3,2,0)

This will return the corresponding letters of ABCMIKE

Thanks Mike done that one.

can you help to this over multi coulmnsA B C D E F

1) A 1 A1 29 A2 582) B 2 B1 30 B2 59

3) C 3 C1 31 C2 60want to put A,A1or A2 In C1 and get the corresponding figures in D1

many thanks if you can help.

Sorry, I don't understand what it is your doing? A before & after would be helpful, also read this How To

on how to use the < PRE > Tags to post data in these forums;http://www.computing.net/howtos/sho...

MIKE

johnrp2, Before posting any more example data in this forum, please click on the following line and read the instructions found via that link. Thanks!

DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A B C D E F 1 A 1 A1 29 A2 57 2 B 2 B1 30 A3 58 3 C 3 C1 31 A4 59 4 D 4 D1 32 A5 60 5 E 5 E1 33 A6 61 6 F 6 F1 34 A7 62

Above is sheet 1

i want be able to select any number

from colums b,d,f into column 4 on sheet 2

A B C D E F1) A 1 B 29 C 57

2) A1 2 B1 30 C1 58

3) A2 3 B2 31 C2 59Above is sheet 1

i want to pull info from column B,D,F into column D on sheet 2

Try this:

Sheet 1 looks like:A B C D E F 1 A 1 A1 29 A2 57 2 B 2 B1 30 A3 58 3 C 3 C1 31 A4 59 4 D 4 D1 32 A5 60 5 E 5 E1 33 A6 61 6 F 6 F1 34 A7 62On Sheet 2 in cell A1 if you enter: A

On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!A1:B6,2,0)

This will return the number 1 from column BThe break down of the =VLOOKUP()

First is the

Lookup_Valueor cell A1 which contains in this case the letter A.

A single item specified by the user.Next is Sheet1!A1:B6 which is the

Table_Arrayor the matrix where we first match our lookup value and from which you want to select your value from.

The Table Array can be two or more columns.Next is the number 2, which is the

Column_Indexthis tell us how many column to the RIGHT to count to get our match.

Since we use the number 2, we count within our matrix starting at column A as 1 column B as 2 so we select the contents of column BLastly is the

Range_Lookupwhich simply says 0 for an Exact Match, or 1 for approximate match, we want an Exact Match so we use 0Now for pulling data from column D using a match from column C:

On Sheet 2 in cell A1 enter: C1

On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!C1:D6,2,0)

This will return the number 31 from column D,

since we specified the 2nd column and or matrix starts at

column C.Now for pulling data from column F using a match from column E:

On Sheet 2 in cell A1 enter: A7

On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!E1:F6,2,0)

This will return the number 62 from column F,

since we specified the 2nd column and or matrix starts at

column E.Can you figure out what we get with this formula:

On Sheet 2 in cell A1 you enter: A

On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!A1:F6,6,0)MIKE

I'm not sure if this is what you are trying to do, but if you want to enter a value that might be in Column A, C or E and return the corresponding value from B, D or F, respectively, then try this: =IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$B$6,2,0)),

IF(ISNA(VLOOKUP(A1,Sheet1!$C$1:$D$6,2,0)),

IF(ISNA(VLOOKUP(A1,Sheet1!$E$1:$F$6,2,0)),"Value Not Found",

VLOOKUP(A1,Sheet1!$E$1:$F$6,2,0)),

VLOOKUP(A1,Sheet1!$C$1:$D$6,2,0)),

VLOOKUP(A1,Sheet1!$A$1:$B$6,2,0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think third-party cookies should be blocked by browsers?

Discuss in The Lounge

Poll History