Hi,
I used your formula in this post ( https://www.myonlinetraininghub.com/excel-search-string-for-a-list-of-words ) to extract the color of an iPhone from a list of possible colors. The problem is the color is occuring multiple times in the list and using the SMALL function as you suggested to return the first instance is not working.
I have a ColorList ranged name that starts in row 1 and am using this function:
=INDEX(ColorList,SUMPRODUCT(ISNUMBER(SEARCH(ColorList,B2)),ROW(ColorList))).
Step by step it evaluates as follows:
=INDEX(ColorList,SUMPRODUCT(ISNUMBER({#VALUE!;#VALUE!;56;#VALUE!;#VALUE!;#VALUE!;#VALUE!;62;#VALUE!;#VALUE!;#VALUE!}),ROW(ColorList)))
=INDEX(ColorList,SUMPRODUCT(ISNUMBER({#VALUE!;#VALUE!;56;#VALUE!;#VALUE!;#VALUE!;#VALUE!;62;#VALUE!;#VALUE!;#VALUE!}),{1;2;3;4;5;6;7;8;9;10;11}))
=INDEX(ColorList,SUMPRODUCT({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11}))
=INDEX(ColorList,0)
But it should be returning INDEX(ColorList, 3)
Can you tell me how to fix this? Thanks!
I have tried it a slightly different way as well, the final two evaluations come out to this:
=INDEX(ColorList,SUMPRODUCT({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}*{1;2;3;4;5;6;7;8;9;10;11}))
=INDEX(ColorList,SUMPRODUCT({0;0;3;0;0;0;0;8;0;0;0}))
=INDEX(ColorList,11) - so it's adding the 3 and the 8...but I need it to only return the 3 position.
Any advice? It seems like the array needs to be in ranked order for SMALL to work - so it fails here.
The final thing I've tried is this:
=INDEX(ColorList, MATCH(TRUE,ISNUMBER(SEARCH(ColorList,$B$2)),0))
This seems to work if I enter it with CTRL+SHIFT+ENTER as an array, which I don't understand....
Hi Anthony,
Welcome to our forum. It's great to see you're trying some different approaches.
The formula in my example is designed to work with a list that contains distinct values, i.e. no colors appearing more than once in your ColorList.
Your last formula should work with or without CTRL+SHIFT+ENTER.
Mynda
