+ Reply to Thread
Results 1 to 22 of 22

Xlookup with multiple criteria

  1. 01-12-2022, 01:08 PM #1
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Xlookup with multiple criteria

    I have a list of Work Centres and I want a formula to return the Region from a table using a xlookup formula. The connection between the work centre and the region will either be the first two digits, the last two digits, or the entire string.
    Please help me find a formula that will search the lookup array for the right connection. I've attached the sample data.

    For example WorkCentre YN07AM01 will be Region Yorkton (identified by the YN at the front of the string)
    WorkCentre C10317YN will also be Region Yorkton (identified by the YN at the end of the string)

    TIA
    Attached Files Attached Files
    Last edited by Mackay2m; 01-12-2022 at 01:25 PM.
    Reply With QuoteRegister To Reply
  2. 01-12-2022, 01:14 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Xlookup with multiple criteria

    Have you considered using a Pivot Table and some Slicers to produce the analysis you want.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 01-12-2022, 01:17 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    You are no longer using Excel 2010. Please update your profile NOW.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 01-12-2022, 01:19 PM #4
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    I would like to pivot the data, but assumed the logical first step would be to fill in the region column in the data first (using a formula instead of manually)
    Reply With QuoteRegister To Reply
  5. 01-12-2022, 01:19 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    We do not require a 3700 row sample. 10-20 rows, WITH EXPECTED ANSWERS, manually calculated is, however, REALLY helpful.
    Reply With QuoteRegister To Reply
  6. 01-12-2022, 01:22 PM #6
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    I've updated my profile and would like to attach a new attachment file.
    Reply With QuoteRegister To Reply
  7. 01-12-2022, 01:24 PM #7
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    Here is the updated test data attachment. Sorry for the confusion
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 01-12-2022, 01:27 PM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    Your profile says Excel 2016... but you talked about XLOOKUP, which came around later. So, please re-confirm the Excel version you are using.
    Reply With QuoteRegister To Reply
  9. 01-12-2022, 01:27 PM #9
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    Thanks Glenn, I've made the changes.
    Reply With QuoteRegister To Reply
  10. 01-12-2022, 01:29 PM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    Sorry, but this isn't much more use. What do you expect to see? Where do you expect to see it?

    You know what you have got in front of you... we don't. So some explanation ise ABSOLUTELY essential.
    Reply With QuoteRegister To Reply
  11. 01-12-2022, 01:34 PM #11
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    A bit of a blind guess...


    =LOOKUP(2,1/(--ISNUMBER(SEARCH($H$2:$H$9,C2))),$G$2:$G$9)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 01-12-2022, 01:38 PM #12
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    Quote Originally Posted by Glenn Kennedy View Post
    Sorry, but this isn't much more use. What do you expect to see? Where do you expect to see it?

    You know what you have got in front of you... we don't. So some explanation ise ABSOLUTELY essential.
    The data I get is just the main work centre for each work order. Column C. I need to use the main work centre to determine which of 8 regions listed in column G where the work is occurring. There are three possible identifiers in the string to determine the region. Either the first two digits, the last two digits or the entire string will be unique. I've tried to illustrate that in the table in column G and H. Does that make sense?
    Reply With QuoteRegister To Reply
  13. 01-12-2022, 01:44 PM #13
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    That is close. For Carlyle, it will be WN07AM02, Weyburn is lookup array "WN" or WN07AM01. So the WN lookup array on it's own, can not be used.
    Reply With QuoteRegister To Reply
  14. 01-12-2022, 01:44 PM #14
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    Quote Originally Posted by Glenn Kennedy View Post
    A bit of a blind guess...


    =LOOKUP(2,1/(--ISNUMBER(SEARCH($H$2:$H$9,C2))),$G$2:$G$9)
    That is close. For Carlyle, it will be WN07AM02, Weyburn is lookup array "WN" or WN07AM01. So the WN lookup array on it's own, can not be used.
    Reply With QuoteRegister To Reply
  15. 01-12-2022, 01:51 PM #15
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    Carlyle can ONLY be WN07AM02

    Weyburn can be WN07AM01 or..... WN at the start? the middle? the end of the string?????
    Reply With QuoteRegister To Reply
  16. 01-12-2022, 01:54 PM #16
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    Quote Originally Posted by Glenn Kennedy View Post
    Carlyle can ONLY be WN07AM02

    Weyburn can be WN07AM01 or..... WN at the start? the middle? the end of the string?????
    Carlyle can ONLY be WN07AM02. Weyburn can be be WN07AM01 OR WN at the end of the string
    Reply With QuoteRegister To Reply
  17. 01-12-2022, 01:54 PM #17
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    Iteration 2:

    =IF(C2=$H$2,"Carlyle",LOOKUP(2,1/(--ISNUMBER(SEARCH($H$3:$H$9,C2))),$G$3:$G$9))

    see file...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  18. 01-12-2022, 02:06 PM #18
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,783

    Re: Xlookup with multiple criteria

    B2=IFERROR(IFERROR(IFERROR(INDEX($G$2:$G$9,MATCH(C2,$H$2:$H$9,0)),INDEX($G$2:$G$9,MATCH(LEFT(C2,2),$H$2:$H$9,0))),INDEX($G$2:$G$9,MATCH(RIGHT(C2,2),$H$2:$H$9,0))),"")

    Copy down
    Reply With QuoteRegister To Reply
  19. 01-12-2022, 02:11 PM #19
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    Quote Originally Posted by Glenn Kennedy View Post
    Iteration 2:

    =IF(C2=$H$2,"Carlyle",LOOKUP(2,1/(--ISNUMBER(SEARCH($H$3:$H$9,C2))),$G$3:$G$9))

    see file...
    Thank you! This works and meets all criteria.
    Reply With QuoteRegister To Reply
  20. 01-12-2022, 02:12 PM #20
    Mackay2m
    Mackay2m is offline
    Registered User
    Join Date
    08-16-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    51

    Re: Xlookup with multiple criteria

    Quote Originally Posted by CARACALLA View Post
    B2=IFERROR(IFERROR(IFERROR(INDEX($G$2:$G$9,MATCH(C2,$H$2:$H$9,0)),INDEX($G$2:$G$9,MATCH(LEFT(C2,2),$H$2:$H$9,0))),INDEX($G$2:$G$9,MATCH(RIGHT(C2,2),$H$2:$H$9,0))),"")

    Copy down
    Thank you! This is what I was trying to accomplish with xlookup. Maybe the iferror piece was what I was missing.
    Reply With QuoteRegister To Reply
  21. 01-12-2022, 02:12 PM #21
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Xlookup with multiple criteria

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  22. 01-12-2022, 04:09 PM #22
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Xlookup with multiple criteria

    Quote Originally Posted by Mackay2m View Post
    I would like to pivot the data, but assumed the logical first step would be to fill in the region column in the data first (using a formula instead of manually)
    Yes, just create a new helper column U and a string slicing formula in U2

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    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] Dynamic array difficulty: LOOKUP/SUMIF/UNIQUE/FILTER/XLOOKUP based on multiple criteria
    By Rabk in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-10-2021, 02:17 PM
  2. Xlookup return maximum value for multiple criteria
    By Excelforum2020 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-09-2021, 10:18 AM
  3. FILTER or XLOOKUP? One criteria in multiple locations; possible multiple results listed
    By akedm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-17-2021, 03:06 PM
  4. Help on XLOOKUP formula with multiple criteria
    By lyzas in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-22-2021, 05:06 AM
  5. Multiple criteria for XLOOKUP formula
    By mikehk in forum Excel General
    Replies: 1
    Last Post: 06-28-2021, 05:50 PM
  6. [SOLVED] xLookup with Multiple Criteria - Finding if a date/time falls within two dates and times
    By stearno in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-24-2021, 10:31 AM
  7. [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

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