Forum Discussion
Solved
Return a Value using Index/Match when the array cell contains multiple entries.
=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?
- 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.
- 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.
=IFNA(INDEX(TMC!$C$2:$C$688,MATCH(B152&"*",TMC!$I$2:$I$688,0)),"")
Thank you,
Glen
