Forum

Notifications
Clear all

Returning first instance of non-zero, non-null item from an Array

4 Posts
2 Users
0 Reactions
51 Views
(@fowzee)
Posts: 3
Active Member
Topic starter
 

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!

 
Posted : 01/09/2019 9:20 pm
(@fowzee)
Posts: 3
Active Member
Topic starter
 

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.

 
Posted : 01/09/2019 9:48 pm
(@fowzee)
Posts: 3
Active Member
Topic starter
 

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....

 
Posted : 01/09/2019 10:32 pm
(@mynda)
Posts: 4761
Member Admin
 

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

 
Posted : 03/09/2019 8:26 am
Share: