# best way to look up text when conditions match

#### nigs

##### New Member
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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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))

That's the one !

Thank you very much for such fast responses.

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

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.

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.

Replies
3
Views
128
Replies
0
Views
78
Replies
1
Views
178
Replies
4
Views
276
Replies
7
Views
264

1,203,323
Messages
6,054,727
Members
444,747
Latest member
Jaborsum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back