Vlookup using partial name

[Fredek]

Hi guys,

I have a question, if I have two tables:

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]133001, 133002, 146203, 171001, 171003, 171010[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]190456, 194782[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]190022[/TD]
[TD]XYZ[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]133002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]171001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]194782[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.