Search code examples
excelexcel-formulamatchworksheet-function

VLOOKUP with criterion in range

I have a table A that contains ID (i.e. SR1000). Also a table B with two rows, another ID (i.e. C-1) and text on the second row.

I was trying to use VLOOKUP using the ID on table A to return ID on Table B if ID from Table A is somewhere in the text of column 2 in table B.

Table A
SR1000
SR1001  

Table B
C-1|dummy text
C-2|dummy SR1000 dummy

So it should return C-2 when looking for SR1000.

Is VBA my only option? Trying to avoid reinventing the wheel.

Solution

  • It seems the formula: 

    =INDEX(F:F,MATCH("*"&B4&"*",G:G,0))

    has been deemed fit for purpose and by way of explanation this was based on assumed locations as below:

    SO2932641 example