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

New Contributor




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 Replies
best response confirmed by Satcom9 (New Contributor)
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.


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.




Thank you,