+ Reply to Thread
Results 1 to 9 of 9

Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

  1. #1
    Registered User
    Join Date
    05-09-2022
    Location
    Singapore
    MS-Off Ver
    MS OFFICE 365
    Posts
    4

    Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    Hello all,

    Apologies I am very new in the Excel world, so some context and example for my question.
    I am just wondering if there is any way to do so.

    E.g
    I want to do a search and match on Sheet 1 for a specific cell with text lets say cell A2 , to check if it matches any cell in a column in
    Sheet 2, then Sheet 3, Sheet 4, if it matches, it will return the value of another column in that row that it matched. If it matches multiple sheets it will return the value of just one, preferably just the one condition stated, example sheet 2.

    I am able to do a INDEX(MATCH) and XLOOKUP to search just sheet 1 against sheet2 and return the value of another column, however I am unable to search multiple sheets. Is there any way to do so?

    Sorry if I am unclear with my query, if more clarification is required please do let me know.
    And thanks in advance for any help you guys can provide.

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,897

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    It's always best to attach a sample workbook for us to work with. See the Yellow banner at the top of this page.
    You can use XLOOKUP, and there's an argument for if "not found". You can embed another xlookup for the next sheet, etc. You might also be able to pull all the sheet together in an array and do the search that way as well.

  3. #3
    Registered User
    Join Date
    05-09-2022
    Location
    Singapore
    MS-Off Ver
    MS OFFICE 365
    Posts
    4

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    Terribly sorry, I have attached a sample that I was working on.
    Under Sheet 4 tab, Column F's coding is what I was able to find.
    However I have no idea how to add more Worksheets into the search term as using if(or) just keeps prompting an error.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,897

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    One option - in Sheet 4, Cell F2 and copied down:

    =XLOOKUP($B2,Sheet1!$B$2:$B$7,Sheet1!$E$2:$E$7,XLOOKUP($B2,Sheet2!$B$2:$B$7,Sheet2!$E$2:$E$7,XLOOKUP($B2,Sheet3!$B$2:$B$7,Sheet3!$E$2:$E$7,"Not found",0),0),0)

  5. #5
    Registered User
    Join Date
    05-09-2022
    Location
    Singapore
    MS-Off Ver
    MS OFFICE 365
    Posts
    4

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    Thank you very much, the code seemed to be working perfectly.
    Really really appreciate the help Gregb11!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

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

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Registered User
    Join Date
    05-09-2022
    Location
    Singapore
    MS-Off Ver
    MS OFFICE 365
    Posts
    4

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    Roger, Thanks I've added rep to Gregb11 just now, adding to yours as well. Thanks again for the help, this Excel community seems amazing.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    There was no need to rep me, as I did not help with the solution, but thank you anyway.

  9. #9
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,897

    Re: Multiple XLOOKUP/INDEX(MATCH) functions to return one value.

    Thanks for the feedback and the rep!

+ 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. Do I use an INDEX/MATCH function or XLOOKUP for this example?
    By Majestic1976 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2021, 08:57 AM
  2. Index/Match vs Xlookup
    By lastnn30 in forum Excel General
    Replies: 3
    Last Post: 11-28-2021, 01:53 PM
  3. XLOOKUP vs INDEX & MATCH?
    By andrewc in forum Excel General
    Replies: 9
    Last Post: 08-26-2021, 10:59 AM
  4. Replies: 4
    Last Post: 11-14-2019, 10:31 AM
  5. Arrary functions and Index(Match)) replicating return.
    By mwensel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2017, 06:31 PM
  6. Replies: 14
    Last Post: 04-09-2015, 12:43 PM
  7. Replies: 6
    Last Post: 07-10-2010, 05:16 AM

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