Array to output col_C with 2-level filtering on col_A and B

Originally Posted by Haseeb A

Try this in H3, copy down.

=IFERROR(INDEX($D$3:$D$10,MATCH(1,IF($B$3:$B$10=G3,IF($C$3:$C$10=SMALL(IF($B$3:$B$10=G3,$C$3:$C$10),1),1)),0)),"")

This is an Array Formula. Copy & paste this in t0 H3, press F2, then press CTRL+SHIFT+ENTER rather than just ENTER. Then you can see the formula covered by {}.

Thanks very much. It works but I'm trying to understand it now.

Firstly, what does the CTRL+SHIFT+ENTER do to it? Just pressing ENTER did not work, what was the difference?
I may have figured this one out...




I'm decomposing your code, correct me if I'm wrong:

=SMALL(IF($B$3:$B$10=G3,$C$3:$C$10),1)
This part basically picks out the rows that have product number 240, "remembers" the dates, and then select the smallest date (ie. 1). Because of $B$3:$B$10=G3, I have to do an array {}? Is that what {} is for?

IF($C$3:$C$10=SMALL(IF($B$3:$B$10=G3,$C$3:$C$10),1),1)
This takes the previously selected date and enters another IF array saying for that particular date in the date column, output "1" against it. THe rest of the array items are stored "".


IF($B$3:$B$10=G3,IF($C$3:$C$10=SMALL(IF($B$3:$B$10=G3,$C$3:$C$10),1),1))
This says for the particular product number "240", with the date that had "1" assigned to it previously, assign a "1" to this row?? The rest of the array items are stored ""??

MATCH(1,IF($B$3:$B$10=G3,IF($C$3:$C$10=SMALL(IF($B$3:$B$10=G3,$C$3:$C$10),1),1)),0)
This looks up "1" in the array (which only had column 5 selected) and outputs the ROW number in the selected array? In this case, it is "3".

INDEX($D$3:$D$10,MATCH(1,IF($B$3:$B$10=G3,IF($C$3:$C$10=SMALL(IF($B$3:$B$10=G3,$C$3:$C$10),1),1)),0))
This outputs the content in D column for row number "3".


Is my interpretation correct?

Thanks.