Frustrating formula issue

[gpicone]

I have 2 tables of data on seperate sheets. There is repeat data on both sheets but 1 set of data contains a column of information that I need to match to the other but the row order of both sheets is jumbled.

I've tried using the "if" formula, however, I need the true value to return another cell several columns over from the value that matches! Any insight?

 

[PaddyD]

hi - welcome to the board.

review the help file for vlookup() - post back with more info if you cnt get it sorted

 

[gpicone]

Thanks for your timely response. I don't think that H or V lookup alone will do it because I can't specify which row the data will be in (it will be in the row that is a match to the lookup value)

Example:

Sheet 1 Sheet 2

A 1 B
B 2 C
C 3 A

From the above, I need to match the 2nd column in Sheet 1 with the corresponding data in sheet 2.

Hope this makes sense.

 

[gpicone]

In last post the 3rd column is supposed to show under "sheet 2" but it won't show correctly

 

[PaddyD]

it is the point of vlookup() & similar function that you don't need to specify the matching positions:

with your data in col a & b of sheet1 * col a of sheet 2, the following in b1 of sheet2:

=vlookup(a1,sheet1!a1:b3,2,0)

would return 2 (if I've got your data set up right)

 

[gpicone]

You're right. It was VLookup. now I have a much easier problem of trying to drag the formula down without moving the Table_array values respectively. Any input on that so that the table_array range is locked in?

 

[PaddyD]

review help file for about cell and range references -

=vlookup(a1,sheet1!$a$1:$b$3,2,0)

 

[gpicone]

You're the man (or Woman)! Thanks a ton.