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

[steam03]

I have been working on this issue for over 3 hours and not able to figure it out although it does sound kind of simple to me . Please help.

Say, I have a table with 3 columns like so...

Capture.JPG


I want the green cells to output the tooling name that has the latest delivery date IN the particular product name group.


How would I make this happen? Please advise.

[Haseeb Avarakkan]

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

[steam03]

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.