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

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

Hi,

Trying to create a formula to create a New table from an Existing table.

It'll have (3) lookup values. You'll need to enter the values in the (lookup values) area.

Load Capacity (Enter one: 500#, 1000#, 2000# or 4000#)
Emission Type (Enter one: Gas, Diesel or Electric)
Horse Power (Enter one: 150, 200 or 250 or 300)

After entering all (3) lookup values, it would check the Existing table for all matching values and display the matching values insides the New table.

I've searched and found I can do this but with only (1) lookup value.

I've attached an excel file showing the (1) lookup value that works for Load Capacity.

Can someone help me out? Is there an easier way, using formulas?  Register To Reply

2. ## Re: 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.  Register To Reply

3. ## Re: Multiple Lookup search in table and display separate table with matched values

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  Register To Reply

4. ## Re: Multiple Lookup search in table and display separate table with matched values

Thanks pepe74287 and JohnTopley!! They both worked!!

******* SOLVED *******  Register To Reply

5. ## Re: **SOLVED** Multiple Lookup search in table and display separate table with matched val

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.  Register To Reply

6. ## Re: **SOLVED** Multiple Lookup search in table and display separate table with matched val

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  Register To Reply

7. ## Re: **SOLVED** Multiple Lookup search in table and display separate table with matched val

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}?  Register To Reply

8. ## Re: **SOLVED** Multiple Lookup search in table and display separate table with matched val 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))),"")  Register To Reply

9. ## Re: **SOLVED** Multiple Lookup search in table and display separate table with matched val

*Mind Blown*

Good call Tony. Thanks   Register To Reply

10. ## Re: **SOLVED** Multiple Lookup search in table and display separate table with matched val

You're welcome!   Register To Reply

11. ## Re: **SOLVED** Multiple Lookup search in table and display separate table with matched val

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.  Register To Reply