+ Reply to Thread
Results 1 to 5 of 5

Lookup specific value with multiple criteria from table

  1. #1
    Forum Contributor
    Join Date
    07-04-2011
    Location
    London, Ontario
    MS-Off Ver
    Excel 365
    Posts
    119

    Lookup specific value with multiple criteria from table

    I am trying to lookup up a specific "item code " if available from the table based on the parameters provided. not sure if multiple vlookups formulas required here?


    SKU Status Year ITEM CODE
    581 STOCK 2014 ?
    828 STOCK 2018 ?
    854 OUT 2016
    927 ORDER 2018
    217 STOCK 2017
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lookup specific value with multiple criteria from table

    Try in N2 copied down...

    =IFERROR(INDEX($B$2:$B$99,MATCH(K2&"|"&L2&"|"&M2,$A$2:$A$99&"|"&$F$2:$F$99&"|"&$E$2:$E$99,0)),"")

    IMPORTANT
    • This is an array formula
    • Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
    • If entered correctly, the formula will be enclosed in {brackets}
    • Do not enter the {brackets} manually
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    07-04-2011
    Location
    London, Ontario
    MS-Off Ver
    Excel 365
    Posts
    119

    Re: Lookup specific value with multiple criteria from table

    Thanks Jeff. Works nicely. Just curious , what is "|" used for in the formula ?

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Lookup specific value with multiple criteria from table

    Glad it worked out for you and you are welcome.

    It's purely a preference thing and you can call it a delimiter.

    As an example...

    Let's say you had Region 1 and Store 23. When you concatenate it becomes 123.

    Now let's say you have, Region 12 and Store 3. When you concatenate it becomes 123.

    In this case you would get invalid results, however, with a delimiter, the results would be 1|23 and 12|3 respectively. The results should be valid now.

    Not saying your data would have that, but just a habit.


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by jeffreybrown; 11-09-2018 at 06:13 PM.

  5. #5
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,277

    Re: Lookup specific value with multiple criteria from table

    Two formulas
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. [SOLVED] Lookup based on Multiple Criteria on Multiple Table
    By oktovan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-05-2017, 04:04 PM
  2. [SOLVED] Formula that can lookup multiple criteria and return a specific value...
    By cateyes_28 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2015, 10:40 AM
  3. Replies: 2
    Last Post: 06-26-2015, 06:04 AM
  4. [SOLVED] Table Lookup - Multiple Criteria
    By jdw001 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2012, 04:56 AM
  5. [SOLVED] Lookup from a table with multiple criteria
    By some99 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-06-2012, 02:00 PM
  6. [SOLVED] How do I use a lookup table to sum values for specific criteria?
    By NJLExcel1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-08-2012, 02:57 PM
  7. Multiple Criteria Lookup in Table
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2012, 03:21 AM

Tags for this Thread

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