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

Hi, there is. Not sure if this one is the best one, but works.
B24=IFERROR(INDEX(B$4:B$19,SUMPRODUCT(SMALL(($D$4:$D$19=$L$3)*($F$4:$F$19=$L$5)*($E$4:$E$19=$L$7)*(ROW($D$4:$D$19)-3),SUMPRODUCT(--(($D$4:$D$19=$L$3)*($F$4:$F$19=$L$5)*($E$4:$E$19=$L$7)=0))+ROW(1:1))),1),"")

and drag across.

Try.. in B24

=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))),"")

Enter with Ctrl+Shift+Enter

Then copy across and down

Thanks pepe74287 and JohnTopley!! They both worked!!

******* SOLVED *******

Hello John,

Do you mind explaining your array formula a little further, specifically why you need to use the SMALL function? I get the 3 arrays that you multiply to match up the 3 parameters, but can you explain the use of SMALL and the rows? Thanks.

When there is match, the formula stores the (relative to start of the range) ) row numbers (ROW($A$4:$A$19)-ROW($A$4)+1) in an array: so we might get 1,3,4,7 as matches. The SMALL function, using the ROWS($A$4:A4) part as the index to the position in the array, selects the first number (1) , then the second( 3) etc.

As the formula is dragged down ROWS($A$4:A4) is increments by 1 (1,2,3 ...) so we get the smallest, then 2nd smallest.

The IERROR is there so that when we have "emptied" the array of row numbers we get a blank rather than an error code.

Click on the formula then click on SMALL then function (fx) and you will see the row numbers displayed: no matches are shown as "",

Hope this helps

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}?

*Mind Blown*

Good call Tony. Thanks

Re

ROW($A$4:$A$19)-ROW($A$4)+1

Using this technique guarantees the row numbers (indices) will start at 1 and we don't have to remember whether to subtract 3 or 4 ....

so row(4) - row(4)+1 = 1 , row(5) - row(4) + 1 = 2 etc

The ROW($a$4) is absolute so remains constant.