+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    05-06-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    2

    **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?
    Attached Files Attached Files
    Last edited by socals13guy; 09-15-2015 at 11:47 AM. Reason: Solved my question & excel formula

  2. #2
    Registered User
    Join Date
    01-28-2014
    Location
    Brno
    MS-Off Ver
    Excel 2010
    Posts
    70

    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.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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

  4. #4
    Registered User
    Join Date
    05-06-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    2

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

    Thanks pepe74287 and JohnTopley!! They both worked!!

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

  5. #5
    Registered User
    Join Date
    07-09-2014
    Location
    Naperville, IL
    MS-Off Ver
    office 2013
    Posts
    29

    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.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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

  7. #7
    Registered User
    Join Date
    07-09-2014
    Location
    Naperville, IL
    MS-Off Ver
    office 2013
    Posts
    29

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

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    Quote Originally Posted by msantucci View Post
    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))),"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Registered User
    Join Date
    07-09-2014
    Location
    Naperville, IL
    MS-Off Ver
    office 2013
    Posts
    29

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

    *Mind Blown*

    Good call Tony. Thanks

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome!

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with table lookup using matched text
    By staticsnake in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2014, 01:33 PM
  2. [SOLVED] Search for multiple string values in the first row of a table and format column values.
    By Excel_junky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2013, 12:48 PM
  3. Lookup in one table and display two values in another
    By pafranklin in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2012, 05:40 PM
  4. Replies: 3
    Last Post: 05-20-2011, 09:13 AM
  5. Display matched table data in another sheet
    By vanir in forum Excel General
    Replies: 2
    Last Post: 08-30-2009, 09:02 PM
  6. lookup values from multiple formula table
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2009, 11:15 AM
  7. Lookup cell value in separate worksheet and return multiple matched values
    By jwhite68 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2007, 12:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1