+ Reply to Thread
Results 1 to 8 of 8

XLOOKUP when the lookup value has comma separated cell

  1. #1
    Registered User
    Join Date
    09-15-2021
    Location
    Australia
    MS-Off Ver
    Microsoft Office 365
    Posts
    35

    XLOOKUP when the lookup value has comma separated cell

    Hello,

    I have a spreadsheet where Column A in Sheet1 has a list of IDs that are separated by commas in a cell. In Sheet2, Column A has a list of unique IDs (not comma separated) that match Column A in Sheet 1. I would like to know how I can use XLOOKUP to find a match from within a comma separated cell in Column A of Sheet1, and once a match is found, to bring the value from Column B in Sheet2 over to Column B in Sheet1? Please see attached an example of the spreadsheet I am referring to.

    I tried =XLOOKUP("*"&A2&"*",Sheet2!A:A,Sheet2!B:B,"") but that did not work.

    Any ideas would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,205

    Re: XLOOKUP when the lookup value has comma separated cell

    XLOOKUP is not very familiar yet, you can use another solution, refer to

    Worksheet name : Sheet1
    Cell B2 formula , Drag down

    HTML Code: 

  3. #3
    Registered User
    Join Date
    09-15-2021
    Location
    Australia
    MS-Off Ver
    Microsoft Office 365
    Posts
    35

    Re: XLOOKUP when the lookup value has comma separated cell

    wk9128, Thank you so much! This formula worked perfectly!
    Last edited by AliGW; 09-28-2021 at 03:55 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,205

    Re: XLOOKUP when the lookup value has comma separated cell

    @Eades1412 You're Welcome. Glad to help . Thank You for the feedback


    If you finally get a solution please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".
    - Please click on the *Add Reputation button at the bottom left.

  5. #5
    Registered User
    Join Date
    09-15-2021
    Location
    Australia
    MS-Off Ver
    Microsoft Office 365
    Posts
    35

    Re: XLOOKUP when the lookup value has comma separated cell

    Hi everyone,

    Thank you to wk9128 who initially helped me with the above formula. This worked with what I was doing before, but now I am having trouble using the same formula for a different scenario. I am not sure if it is the formatting, as the numbers I am trying to match have leading zeros in it. Basically, my lookup value is a bunch of IDs that are separated by commas in one cell. I would then like to see if that matches an ID from a separate worksheet, which has leading zeros in it. The formula above, unfortunately did not work. Could it be something to do with the formatting of my numbers? See attached worksheet for an example.

    The bold IDs in column A of Sheet1 are the IDs I am trying to match. Sheet2, Column A has the 'WOS:' IDs that I am matching to (I have formatted these using the 'Custom' formatting as they were automatically defaulting to scientific numbers. I would then like to bring across data from Column B, Sheet2 onto Sheet1.

    Any ideas where I am going wrong on this occasion?

    Thanks in advance!
    Sophia
    Attached Files Attached Files

  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
    61,942

    Re: XLOOKUP when the lookup value has comma separated cell

    Try this:

    =IFERROR(LOOKUP(1000,SEARCH(Sheet2!$A$1:$A$5,A1),Sheet2!$B$1:$B$5),"")

    Next time you have a new query, please start a new thread.
    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
    09-15-2021
    Location
    Australia
    MS-Off Ver
    Microsoft Office 365
    Posts
    35

    Re: XLOOKUP when the lookup value has comma separated cell

    Thanks for your help, AliGW! This formula worked a treat! Also, no problems regarding starting a new thread next time I thought it might have been regarding the same formula, so wasn't sure whether to start a new one or not. Will keep that in mind for next time.
    Last edited by AliGW; 10-04-2021 at 03:08 AM. Reason: PLEASE don't quote unnecessarily!

  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
    61,942

    Re: XLOOKUP when the lookup value has comma separated cell

    Thanks for 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. Replies: 4
    Last Post: 12-01-2020, 01:20 PM
  2. Lookup & return multiple values in one cell, separated by comma
    By cav0731 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2020, 10:51 AM
  3. [SOLVED] Sum values using vlookup with lookup cell containing comma separated values
    By tmalito in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-08-2019, 10:14 AM
  4. Replies: 16
    Last Post: 11-07-2018, 05:03 AM
  5. How to lookup a value in one column to create a comma separated list
    By powersml07 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-05-2018, 02:16 PM
  6. Comma separated list with vertical lookup
    By tamxc4 in forum Excel General
    Replies: 0
    Last Post: 04-13-2012, 11:05 AM
  7. Return Y/N after matching from multiple lookup values separated by comma
    By indoglans in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2011, 03:13 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1