Vlookup using partial name

[Fredek]

Hi guys,

I have a question, if I have two tables:

Table 1

133001, 133002, 146203, 171001, 171003, 171010	ABC
190456, 194782	DEF
190022	XYZ

<tbody>
</tbody>

Table 2

133002
171001
194782

<tbody>
</tbody>

I would like to do a some sort of vlookup using column A Table 2 to return column B of Table 1. Is this possible?

 

[NARAYANK991]

Hi ,

You can use wildcards , as follows :

=VLOOKUP("*" & Table2 value & "*", Table1, 2, FALSE)

Here , Table2 value would be a reference to 133002 or 171001 or 194782.

Table1 would be a reference to the two columns of data which you have labelled as Table 1.

 

[Aladin Akyurek]

Let A:B house the data of Table 1 and column F the data of Table 2.

In G2 enter and copy down:

=IF($F2="","",IFERROR(LOOKUP(9.99999999999999E+307,FIND(F2&",",$A$2:$A$4&","),$B$2:$B$4),"not found"))

 

[NARAYANK991]

Hi ,

Ignore my earlier post because the formula fails in the third case , where 190022 is a lone entry.