+ Reply to Thread
Results 1 to 20 of 20

Xlookup when both the lookup value and lookup array are separated by commas

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

    Xlookup when both the lookup value and lookup array are separated by commas

    Hello everyone,

    I would like to find a formula like XLOOKUP which looks up say, column A from Sheet 1 to see if there's a match in Column A from Sheet 2, and returns column B from Sheet 2. The problem is, Column A from both worksheets have values which are separated by commas.

    See attached my example below. I would like to know if any of the values in column B of Sheet 1 matches any of the values in Column B of Sheet 2, then return Column A from Sheet 2 to Column C from Sheet 1, if that makes sense!?

    Thanks in advance for your help!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Please manually add at least 10 rows of expected data to the workbook, including at least ONE row where there are two lookup values. Thanks.
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    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
    43,893

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Your sample is simply mad. 10,000+ rows. Manual checking is impossible.

    Doesn't

    =IFERROR(INDEX('Sheet 2'!A:A,MATCH(B3,'Sheet 2'!B:B,0)),"")

    work?

    If not 10-20 rows MAXIMUM shiowing instances where it does NOT work, please.
    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

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Hi AliGW, I have attached a further example, including one row where there are two lookup values. Hopefully this helps. Thanks.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    See Glenn's post - your lookup dataset is far too big, making manual checking of solutions offered next to impossible. Please address this.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Doesn't a simple VLOOKUP do the job?

    =IFNA(VLOOKUP(B3,'Sheet 2'!$B$1:$C$10133,2,0),"Not Found")

  7. #7
    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
    43,893

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    You asked for data from column A. Not it seems that it is column C:


    =IFERROR(INDEX('Sheet 2'!C:C,MATCH(B3,'Sheet 2'!B:B,0)),"")

    As there are still 10,000+ rows, I have not done any manual checking. One journal seems as though it can have more than 1 ISSN... but I assume that 1 ISSN applies to one journal only. Herence, there is no complication here at all.

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Hi Glenn Kennedy. Apologies, I overlooked your original reply yesterday. I have attached an updated example with a much smaller dataset.

    Sorry, I confused everyone in the first place. Please disregard my previous replies. I will try to explain better using this example attached.

    What I am trying to find is the values from Column C of Sheet 2 (the Journal Type). I want to use Column B from Sheet 1 and Column A from Sheet 2 as match points. However, the tricky part about matching these two columns is that the ISSNs are separated by commas.
    Basically, I want ANY ISSN in Column B from Sheet 1 to match ANY ISSN from Column A of Sheet 2. It does not have to be an exact match of all the ISSNs in one cell.
    So for example, if one column has 1234-1234, 1111-1111 and the other column has only 1234-1234, then I would want this to be a match, if that makes sense?

    AliGW's Vlookup works somewhat in this scenario, but not completely, as it matches to the exact values in the cell. I want it to match at least one ISSN in a cell. Hope that makes sense?

    My example attached shows what the correct value in Column C (Sheet 1) should be. Column D (Sheet 1) is using the Vlookup formula and I have highlighted the incorrected values.

    Thank you!
    Attached Files Attached Files

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    So you could have 10 comma-delimited entries in one cell in Col B of Sheet 1, and 10 comma-delimited entries in another cell in Col A of sheet 2, and if the 4th entry of the comma-delimited list in the cell in Col B, sheet 1 matched the 8th comma-delimited entry in that cell in Col A of Sheet 2, then that's a match and you want to show the journal Type of that row?

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Gregb11 yes, that's correct. But any ONE of those ISSNs can be a match. It doesn't have to be all 10 of them if that makes sense!
    Thanks!

  11. #11
    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
    43,893

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Any reason why you just don't use the journal title??

    =IFERROR(VLOOKUP(A2,'Sheet 2'!B:C,2,FALSE),"Not Found")

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    AliGW's Vlookup works somewhat in this scenario, but not completely, as it matches to the exact values in the cell.
    My point, really, was that it did everything you wanted in the sample file we had at that point.

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Glenn Kennedy Matching by title is possible, but not ideal because the two sheets come from two different systems, so the title is not always an exact match. Using a unique identifier gives me a more accurate match, but if there is no solution to this, I may just need to use the title instead.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Why don't you normalise the dataset? Having multiple IDs in one cell is not good practice. You could easily convert your lookup list using PowerQuery and then make sure that it's one ID per record going forward.

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    AliGW, I've not used Power Query much, but will have a look into it. I have split the ISSNs into separate columns and done a match that way before, but because there could potentially be up to 6 ISSNs in each sheet, I wanted to avoid doing a match 36 times Maybe Power Query would be a better option for this.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Have a look at the attached. I have normalised the lookup data into a new table on the lookup tab and then used that for the lookup. Let me know if you think this would work and I'll explain how to do it (it all uses UI interface clicks in PQ - nothing fancy).
    Attached Files Attached Files

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Yes, this is exactly what I need! So the idea is to place each ISSN (with their corresponding titles) on a separate row, then match it on that table?

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Yes - that's precisely it. Normalised means ONE ROW per record. Are you able to understand the query I set up to do the normalisation?

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

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Yes, I think I have seen this done before on Power Query, so will experiment with it and do it that way. Thanks so much for your help, AliGW!

    Also, thank you to everyone else who responded to my query!

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Xlookup when both the lookup value and lookup array are separated by commas

    Just shout if you need help, but in the meantime, 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 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.

+ 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] XLOOKUP when the lookup value has comma separated cell
    By Eades1412 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-06-2024, 11:43 AM
  2. Replies: 7
    Last Post: 12-10-2021, 02:17 PM
  3. Lookup xLookup or something..
    By xKelly in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2021, 09:24 PM
  4. Replies: 7
    Last Post: 07-16-2021, 11:04 AM
  5. [SOLVED] How to convert part of array column to text, separated by commas?
    By rcurious in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2021, 02:53 AM
  6. Replies: 3
    Last Post: 03-10-2017, 07:56 PM
  7. [SOLVED] How to shorten code into an array to create a list of items separated by commas
    By mdovey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2014, 12:23 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