+ Reply to Thread
Results 1 to 12 of 12

XLOOKUP OR INDEX/MATCH based on two column criteria.

  1. 02-22-2024, 06:31 AM #1
    CPAC
    CPAC is offline
    Forum Contributor
    Join Date
    05-14-2015
    Location
    West Yorkshire
    MS-Off Ver
    10
    Posts
    130

    XLOOKUP OR INDEX/MATCH based on two column criteria.

    Hi I'm trying to return the correct Pack Price in the attached file using the SIZE & QUANTITY. I've so far come up with the following that I can't get to work. Any help would be appreciated. Thanks.

    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 With QuoteRegister To Reply
  2. 02-22-2024, 06:50 AM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,731

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    You could do it this way:

    =INDEX(PRICES!G4:G30,MATCH(1,(PRICES!B4:B30=F4)*(PRICES!A4:A30=F2),0))

    Note that this is an array formula, so if you are using XL2019 or earlier you must confirm using the key combination of Ctrl-Shift-Enter rather than the usual Enter.

    As you quote formulae using XLOOKUP and XMATCH I suspect you are using a more recent version, so please update your profile to show this.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  3. 02-22-2024, 06:52 AM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    Try:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 02-22-2024, 06:56 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    With XMATCH:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-22-2024, 07:01 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    @Pete: for some reason, I needed to coerce the Quantity to be a numeric value
    Reply With QuoteRegister To Reply
  6. 02-22-2024, 07:04 AM #6
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,731

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    Yes, I did as well. The drop-down didn't work for me, but it uses the function ANCHORARRAY() which I don't have.

    Pete
    Reply With QuoteRegister To Reply
  7. 02-22-2024, 07:24 AM #7
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,206

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    Try

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



    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  8. 02-22-2024, 07:27 AM #8
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,731

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    Hi John,

    I see you have updated to XL365 - no stopping you now !!

    Pete
    Reply With QuoteRegister To Reply
  9. 02-22-2024, 07:35 AM #9
    CPAC
    CPAC is offline
    Forum Contributor
    Join Date
    05-14-2015
    Location
    West Yorkshire
    MS-Off Ver
    10
    Posts
    130

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    Amazing!! Thank you all so much for your time.
    Reply With QuoteRegister To Reply
  10. 02-22-2024, 07:35 AM #10
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,206

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    Hi Pete,
    New laptop so had no choice but to upgrade so I am now working my way through a (very good) YOU TUBE video on the new functionality.

    And using the forum to test how much I have learned: hence my reply to this post. Small steps

    John
    Reply With QuoteRegister To Reply
  11. 02-22-2024, 07:41 AM #11
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,731

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    @CPAC,

    Thanks for the rep. PLEASE update your profile while you are here.

    Pete
    Reply With QuoteRegister To Reply
  12. 02-22-2024, 07:56 AM #12
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,447

    Re: XLOOKUP OR INDEX/MATCH based on two column criteria.

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] XLOOKUP OR INDEX/MATCH based on two criteria.
    By CPAC in forum Excel General
    Replies: 6
    Last Post: 02-21-2024, 12:53 PM
  2. [SOLVED] xlookup or index match from another worksheet from 2 or 3 criteria
    By Jubster in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-07-2024, 05:00 AM
  3. [SOLVED] Output a Value Based on Two Other Columns - XLOOKUP/INDEX-MATCH?
    By jk2391 in forum Excel General
    Replies: 3
    Last Post: 10-17-2022, 02:06 PM
  4. [SOLVED] XLOOKUP to return cell based on multiple criteria in single column
    By mikehay08 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2021, 02:59 PM
  5. Index Match based on three criteria (2 rows one column)
    By Katie620 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2020, 05:47 PM
  6. Index Match :I want to retrieve all data in one column based on criteria shown in picture.
    By manish2303 in forum Excel General
    Replies: 8
    Last Post: 09-02-2019, 04:12 PM
  7. [SOLVED] complex if with index & match formula based on criteria in a column to extract data
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2018, 01:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1