+ Reply to Thread
Results 1 to 12 of 12

How can I lookup items with multiple values?

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    How can I lookup items with multiple values?

    Hello, I've only recently started using Excel and I'm stuck...
    (English is my native language, that's why I didn't submit this question in the Other Languages threads. The sheets I'm making contain Chinese and English but you can just ignore the Chinese words)
    This is my sheet with all the data. Sheetname: "PRICELIST單價表"
    1.JPG

    This is the sheet where I need to lookup data. Sheetname: "TENDERLIST"
    2.JPG


    I need to lookup "UNIT PRICE", but instead of just referring to "ITEM CODE" I need it to also refer to "CLASS TYPE" because my unit price varies with different class type. (EG: ITEM CODE: CL00112, C1 PRICE: $1, C2 PRICE: $2, C3: $3, C4: $4 etc.)

    So I need an equation that can change my UNIT PRICE in correspondent to my CLASS TYPE.

    Right now the equation for UNIT PRICE is =VLOOKUP(A20:A99,PRICELIST單價表!A3:E23,5,FALSE)

    Thank you!
    Last edited by imaperson; 06-28-2018 at 03:57 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: How can I lookup items with multiple values?

    In order to look up Class Type we need to know where the other Class Types are.

    Are you saying further down Tenderlist the Class Type changes to say C2 but the format of the Class Type data stays the same?
    Or maybe Class Type C2 is to the right of this image on the spreadsheet.
    Or on a different spreadsheet.
    Or maybe it's something different.
    You havent said this so I guessing.

    Bottom line: when doing any form of Lookup you need to indicate where the data to lookup changes.

    Post a sample spreadsheet, remove any sensitive data, create a mockup example if necessary.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Last edited by Special-K; 06-28-2018 at 04:38 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    06-28-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: How can I lookup items with multiple values?

    Thanks for the reply. Following is my sample spreadsheet, I've included my inquiry within the file.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How can I lookup items with multiple values?

    Try this, copied down as needed...
    =IF(A4="","",INDEX(PRICELIST!$E$4:$I$5,MATCH('TENDER DOCUMENT'!$A4,PRICELIST!$A$4:$A$5,0),MATCH('TENDER DOCUMENT'!$B$1,PRICELIST!$E$3:$I$3,0)))

    Note, I only used the ranges from your sample file, adjust those as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How can I lookup items with multiple values?

    I interpreted a little differently in case Item codes have more than one Unit.

    =IFERROR(INDEX(PRICELIST!$E$4:$I$5,MATCH(1,INDEX((A4=PRICELIST!$A$4:$A$5)*(C4=PRICELIST!$C$4:$C$5),0),0),MATCH($B$1,PRICELIST!$E$3:$I$3,0)),"")
    Dave

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How can I lookup items with multiple values?

    It's possible, Dave, but I would have thought Item code would be unique - guess we will have to wait to hear back from OP

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How can I lookup items with multiple values?

    Yeah. It's probably another episode of me using a belt and suspenders. LOL

  8. #8
    Registered User
    Join Date
    06-28-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: How can I lookup items with multiple values?

    Thanks FDibbins for your reply.
    I tried using your code, it works for Class 1 but it doesn't work for the other Classes.

    I pasted the code in the UNIT PRICE cell.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How can I lookup items with multiple values?

    In 'PRICELIST' sheet the C2 has a trailing space. Check your other data as well for more leading/trailing spaces.

  10. #10
    Registered User
    Join Date
    06-28-2018
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: How can I lookup items with multiple values?

    YES! IT WORKS! Thank you so much FDibbins and FlameRetired!!!

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How can I lookup items with multiple values?

    Happy to help

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How can I lookup items with multiple values?

    Same here, and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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 multiple items
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2016, 08:27 AM
  2. Lookup multiple items
    By shauncasey in forum Excel General
    Replies: 8
    Last Post: 02-12-2015, 03:04 PM
  3. Replies: 1
    Last Post: 09-04-2014, 12:14 PM
  4. Lookup items with multiple Values
    By HabsFan89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-27-2014, 02:15 PM
  5. Looking up multiple items with lookup values occuring more than once
    By FredFitzgerald in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-20-2014, 06:33 AM
  6. [SOLVED] Return multiple items from two and three lookup values
    By bjnockle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2013, 01:14 PM
  7. Multiple Lookup Items
    By choke in forum Excel General
    Replies: 1
    Last Post: 06-18-2012, 01:29 PM

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