Wildcard text match using INDEX and MATCH

**mmayna03** · 09-12-2012, 02:45 PM

I received this wonderful script last week and I now have a new requirement put on me.

Is there a way to make a wildcard search for a single word against another cell with multiple words work in the following formula?

Formula in the Tracks!G3 cell:

=IF(DutyPositions!$O$3="","",INDEX(DutyPositions!O$3:$O1000,MATCH(1,INDEX((DutyPositions!$K$3:$K$1000=H78)*(DutyPositions!$L$3:$L$1000=I3),0),0),1))

I need to have the red highlight be able to pull data based upon the cells in DutyPositions!L3:L1000 which has 3 words per cell pull the data against a single word that matches one word from the Tracks!I3 cell.

DutyPositions!L3:L1000 has three words: Aerospace Education Officer
Tracks!I3 will have only Aerospace in it.
Need to have the formula match th Aerospace (I3) match the Aerospace Education Officer (L3:L1000)

Thank you.

Michael

**NBVC** · 09-12-2012, 02:50 PM

Try:

=IF(DutyPositions!$O$3="","",INDEX(DutyPositions!O$3:$O1000,MATCH(1,INDEX((DutyPositions!$K$3:$K$1000=H78)*(ISNUMBER(SEARCH(I3,DutyPositions!$L$3:$L$1000))),0),0),1))