**SOLVED** Multiple Lookup search in table and display separate table with matched values

Originally Posted by msantucci

Thank you sir, much appreciated! One more quick question if you don't mind - when I press F9 on ROW($A$4:$A$19), and then F9 on ROW($A$4)+1, I see the below array which I know is not correct.

{4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}-{5} --------> {-1;0;1;2;3;4;5;6;7;8;9;10;11;12;13;14}

However, highlighting the whole ROW($A$4:$A$19)-ROW($A$4)+1 gives me the correct 1 through 16 array, how do you know how excel will interpret and build these? In other words, why does excel first subtract the ROW($A$4) and then add 1 to the entire array, rather than just subtracting {5}?

I'll let John answer that particular question.

However, if you index the entire column then you don't have to "worry" about that!

=IFERROR(INDEX(B$4:B$19,SMALL(IF(($D$4:$D$19=$L$3)*($F$4:$F$19=$L$5)*($E$4:$E$19=$L$7),ROW($A$4:$A$19)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")

=IFERROR(INDEX(B:B,SMALL(IF(($D$4:$D$19=$L$3)*($F$4:$F$19=$L$5)*($E$4:$E$19=$L$7),ROW($A$4:$A$19)),ROWS($A$4:A4))),"")