best way to look up text when conditions match

[nigs]

Hi, I am sure this is going to be an easy one, but can seem to get the right function to start with as this is all relating to text. can someone help me with the best way to code this:

I have a table named X with text such as
A B
1 nut London
2 bolt Birmingham
3 washer Oxford
and a second table named Y with:
A B
1 washer
2 nut
3 hexnut

I need a formula in table Y to look in table Y range A1:A3 and to show the contents of column B in table X where the range A1:A3 finds a match.
ie Table Y, cell B1, it should result with the word "Oxford" .
Thank you

 

[Richard Schollar]

Hi

That's a job for VLOOKUP:

=VLOOKUP(tableY!A1,tableX!$A$1:$B$3,2,0)

assuming tableY and tableX are two separate sheets. Note that if a match isn't found then the formula will return a #N/A error so assuming you don't want this you can use the following:

=IF(ISNA(MATCH(tableY!A1,tableX!$A$1:$A$3,0)),"Whatever value you want with no match",VLOOKUP(tableY!A1,tableX!$A$1:$B$3,2,0))

 

[nbrcrunch]

you may also be able to modify my reply to the following thread

http://www.mrexcel.com/forum/showthread.php?t=379324

 

[nigs]

That's the one !

Thank you very much for such fast responses.

 

[nigs]

One slight complication i have....

where you have ,2,0)) at the end of the formula making reference to the column location, in my real situation my result field is in the column to the left rather than to the right and ,-1,0)) or ,-2,0)) I thought might work but does not resolve it.
I can not change the column layouts, so how would you code that?
Thank you

 

[nbrcrunch]

vlookup will only look to the right. Try changing the formula to merely "LOOKUP"

If that doesn't work, then you'll need Index/Match combo.

 

[nigs]

That did it, using lookup instead of vlookup and replacing the ,2 with the range of cells that I wanted it to select the match from.
Thanks.