Comma Separated Strings - Multi Criteria Lookup help

[slick9456]

I am trying to lookup a value in a spreadsheet where multiple criteria is involved. Normally I'd approach this task with an index match function, however I am not 100% on how to get the formula started given that in this situation, some of the values being looked up exist in a cell with multiple criteria again separated by commas. For example sheet 1 below would be the data I'd receive and Sheet 2 would be the data I would check it against. Also the cells with comma separated values may have multiple options that also may be relatively similar to other options in the string so an exact match has to be made.

Any guidance on how to approach this type of look up would be greatly appreciated as I've been unable to find anything so far by scouring the internet and threads.


Sheet 1

A	B	C	D	E	F
Tax	New York	BA	Core	Assoc	Salary

<tbody>
</tbody>

Sheet 2

A	B	C	D	E	F
Tax	New York	BA,MBA,BBAMBA	Core,Core2,Secondary	Assoc	Salary

<tbody>
</tbody>

 

[mubashiraziz]

Try this

=ISNUMBER(SEARCH(" "&D1&" "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet2!D1,","," "),";"," "),"!"," ")&" "))

 

[slick9456]

I tried that formula and it works when checking to the value of D1 to a specific cell on Sheet 2, however is there anyway to incorporate this into a multiple criteria lookup formula such as index match where all the cells in row 1 from sheet 1 are positive matches? The issue here is that on sheet 2 there could be hundreds rows. The goal of this formula would be to return the value in column F.

 

[mubashiraziz]

I think it ll be helpful for you ...

https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=60445