+ Reply to Thread
Results 1 to 10 of 10

Chained xlookup and reverse partial lookup/search?

  1. #1
    Registered User
    Join Date
    03-28-2019
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    4

    Chained xlookup and reverse partial lookup/search?

    Hi,

    I have a table of around 10k SKUs that I need to first match the prefix on another table (easy bit as I've included a prefix column on both) but then those matches need to partially match the colour suffix in only those corresponding adjacent cells to return the partially matched Colour code from another column in the table. I envisaged a chained xlookup (I have a 365 account), lookup/search but can't figure out how to pair the two to restrict the partial lookup to only the matching prefix rows in the range. The reason for this is there are many duplicate suffixes/colour codes for different prefixes that return incorrect values if the second reverse partial lookup searches the whole range.

    Please see attached workbook with example ranges on tabs A and B.

    Hoping I can get some urgent help with this.

    Thanks in advance.
    Last edited by paventiger; 04-29-2021 at 01:33 PM. Reason: Didn't include all relevant information

  2. #2
    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: Chained xlookup and reverse partial lookup/search?

    Hi,

    One way.
    Add a helper column C to the 2nd sheet
    C2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then on the first sheet use in D2 copied down

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Chained xlookup and reverse partial lookup/search?

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

  4. #4
    Registered User
    Join Date
    03-28-2019
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Chained xlookup and reverse partial lookup/search?

    Thanks Richard, this worked with the example I gave but I neglected to say that the suffix/colour codes can be anything, including just letters. Sorry for missing out this important information. I have revised the attachment to include a letters only example. Sorry for any inconvenience.

  5. #5
    Registered User
    Join Date
    03-28-2019
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Chained xlookup and reverse partial lookup/search?

    Thanks, this worked with the example I gave but I neglected to say that the suffix/colour codes can be anything, including just letters. Sorry for missing out this important information. I have revised the attachment to include a letters only example. Sorry for any inconvenience.

  6. #6
    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: Chained xlookup and reverse partial lookup/search?

    Is there a typo since the colour suffix C11 on sheet A is GLD whereas on B it is GOLD. If you change C11 on the first sheet to GOLD then

    Try the C2 formula

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


    If the suffic can't be changed then you'll need a lookup table that can convert GLD to GOLD and the formula will need modifying to use the Lookup.

    Incidentally would you add your Excel version to your profile. If you have Excel Office 365 then you have other functions available.

  7. #7
    Registered User
    Join Date
    03-28-2019
    Location
    Nottingham, UK
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Chained xlookup and reverse partial lookup/search?

    It's not a typo and in fact there all sorts of suffixes including characters such as / , _ and - and colour codes that have the same and not always matching, very messy historical product data. Partly the reason I'm performing this task. Nevertheless this solution does help with a great deal of the lines. Thanks again and have a good weekend.

  8. #8
    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: Chained xlookup and reverse partial lookup/search?

    Quote Originally Posted by paventiger View Post
    It's not a typo and in fact there all sorts of suffixes including characters such as / , _ and - and colour codes that have the same and not always matching, very messy historical product data. Partly the reason I'm performing this task. Nevertheless this solution does help with a great deal of the lines. Thanks again and have a good weekend.
    In that case it's probably advisable to create a lookup table which will avoid complex formulae to handle string slicing.

  9. #9
    Registered User
    Join Date
    09-14-2021
    Location
    indonesia
    MS-Off Ver
    Office 2021 (LTSC)
    Posts
    4

    Re: Chained xlookup and reverse partial lookup/search?

    sorry I'm late in responding to your help, and I've tried to fill in your table regarding the color according to the code by using the xlookup function and the xlookup function I have entered in your excel file and I sent it back to you, but in my profile I can't add the file in attachmenst so can I ask for your email so I can send the file via your email. hope it can help you..thank you..

  10. #10
    Registered User
    Join Date
    09-14-2021
    Location
    indonesia
    MS-Off Ver
    Office 2021 (LTSC)
    Posts
    4

    Re: Chained xlookup and reverse partial lookup/search?

    or in the color code column, fill in the xlookup function as follows: =XLOOKUP(A!B2;B!$A$2:$A$11;B!$B$2:$B$11)

+ 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] Using IF, Xlookup and Match/search, to find value in array.
    By Lee_of_Excel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2020, 12:09 AM
  2. [SOLVED] Reverse lookup. Search header/title from the data
    By babychai in forum Excel General
    Replies: 3
    Last Post: 06-10-2020, 11:55 AM
  3. Reverse lookup with only one search criteria
    By 3SixOne in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2019, 09:08 AM
  4. Replies: 4
    Last Post: 07-27-2013, 07:38 AM
  5. Reverse Partial match lookup
    By Stueymac in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-05-2013, 05:12 AM
  6. Reverse Table Lookup using a partial string
    By pat3white in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-27-2013, 01:35 PM
  7. Extended If+Search, aka Partial Lookup
    By .plaid in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2006, 03:14 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