Return a Value using Index/Match when the array cell contains multiple entries.

[Satcom9]

=IFNA(INDEX(TMC!$C$2:$C$688,MATCH(B152,TMC!$I$2:$I$688,0)),"")

Using this formula I was attempting to get the value from column C when the lookup value matched a zone name from column I. In some instances Column I has multiple zone name entries, at which point my formula returns nothing. If column I has a single zone name and it matches my search then it does return Column C info correctly.  Is there any formula that can discern a single name such as DISLCH1609 when it is contained in a cell with many other names?

[#2 · Accepted solution]

Use XLOOKUP() with wildcard matching option. What is your Excel version? FILTER() with SEARCH() should also work. Share a sample workbook so that we can work on that.

[#3]

@Harun24HR 

I am using excel 2016 that does not have xlookup or filter. However your suggestion of a wildcard did lead me to a solution using Match.

Adding &"*" to the formula now returns a match. So Thank you for steering me in the correct direction.

I will be sure in the future to share a sample workbook that will make this process easier for all involved.

=IFNA(INDEX(TMC!$C$2:$C$688,MATCH(B152&"*",TMC!$I$2:$I$688,0)),"")

Thank you,

Glen