XLOOKUP Challenge

[jsmullikenfl]

Hey folks, 

         I've got a problem that I don't know how to solve, with a spreadsheet.  The source sheet that I'm trying to work with, contains a column for "Sales Order Number", that contains from two to four different SO #'s, as a result of how our ordering system works.  Each line item on a quote, generates a separate sales order.  I want to use a single SO# (from a different sheet) as the "what to look for" value in XLOOKUP, and have it search for that SO# in that column that has those 2 to 4 values in each row, and then return to me the value in a different column in that row.  The 2 to 4 values are separated by spaces, in some cases, semicolon's in others, and comma's in still others.  

      Any suggestions as to how I might accomplish this task?    

      Thanks,   

       Jeff

[#2]

Hello Jeff,
I need some clarifications:
A) When you say "contains from two to four different SO #'s," does that mean you have 2 to 4 SO#s like 1234 5678 9012 3456 or that the PO#s can be from 2 to 4 digits long like 23 456 7890 23?
B) When you say "The 2 to 4 values are separated by spaces, in some cases, semicolon's in others, and comma's in still others. " you can solve that by using a helper column that has a formula like this =SUBSTITUTE(SUBSTITUTE(A1,";",",")," ",",") to change all into a comma to standardize things.
C) Then you can use an XLOOKUP with a Match Mode value 2 to use wild cards to match any of the segments you have as lookup values.
Look at this one for more details. https://support.microsoft.com/en-us/office/using-wildcard-characters-in-searches-ef94362e-9999-4350-...

And this may help: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Hope this helps
Georgie

[#3]

@jsmullikenfl 

I would need to see a sample of the data, but XLOOKUP is capable of performing 'wildcard' matches.

= XLOOKUP("*"& SOrequired &"*", SOnumbers, SOdata,"not found",2)

using a value of 2 as the 'match_mode'.

[#4]

@GeorgieAnne Thanks Georgie.  So, an SO # for us, is 9 digits long.  The folks who put the source spreadsheet together, created a column labled "SO #", and then put from a single 9-digit SO#, to as many as four of them, in that single cell.  And again, they weren't even consistent with separators, using commas or semicolon's.  I want to be able to have XLOOKUP take a single 9-digit SO# from a different sheet, and find it in that column, regardless of whether that # is in any of the 4 possible positions in that column range, and then return a value from a different column in the row in which it was found.  Is that any clearer?    Cheers,   Jeff 

[#5]

Hello@jsmullikenfl 

Yes I guess now I understand that the SO#s can be one or many in a single cell.

So, first we need to separate these SO#s so that we have a single SO#  in each cell. This will require a new worksheet that would house the "Clean"ed Data.

A) As I mentioned, you can use the SUBSTITUTE() function to make all the delimiters the same.

B) Then we need to separate each of these SO# into a single column so we will need to use TEXT TO COLUMNS in the DATA section of the  Ribbon.

C) Then we need to get all these separated SO#s into a single column so we need the TRANSPOSE() function.

D) Then we can use an XLOOKUP() with wildcards to match each separated SO# from the old worksheet to the new "Clean"ed data worksheet.

I am hoping that each SO# combined with others in that same cell will be corresponding to the same order data. Otherwise well... who will know!

Once you have the SO#s separated marring the data should not be so difficult.

If you can post a worksheet with say 20 or so rows, we can see the patterns and possibly have better solutions for you.

Have a great day!

[#6]

@jsmullikenfl 

Without seeing the data, I offer this solution:

=XLOOKUP("*"&E2:E4&"*",A2:A5,B2:B5,"",2)

[#7]

@jsmullikenfl 

I am still not completely sure where this is going without sight of some sample data.  The replies establish that it is possible to lookup data by searching for text within a string.  It is also possible to separate the compound strings into a list of sales order numbers by formula.  I would use a Lambda function for the formula simply to keep the workbook tidy.

where

SplitOrderλ 
= LAMBDA(SOnum,
    DROP(
        REDUCE(
            "",
            SOnum,
            LAMBDA(list, SOs,
                LET(splitSO, TEXTSPLIT(SOs, , " "), VSTACK(list, splitSO))
            )
        ),
        1
    )
)

Of course, all this requires 365, but my normal advice to users of older systems is discard them as soon as you can afford to.  Eventually the phrenetic rate of change must slow, but now is not the time to be stuck with obsolete software. 

[#8]

@Peter Bartholomew 

First, to everyone who has offered their help with this, I want to say "Thank you!".  It renews my faith in humanity to see people like you offer your time and expertise to help a complete stranger.  I do appreciate it.  So, I have included a bit of the spreadsheet, so that you can see the structure.  So, from a different, call it "master" sheet, which contains a list of all SO#'s, I'm trying to use a single SO# as the first parameter in the XLOOKUP, or "what to look for", and using column "G" as the range to search.  Then, I'm wanting to return the values in columns "A" and "B", back to the master sheet.  But for me, as there are multiple SO#'s in each cell in column G, I'm getting errors.  I hope this clarifies the problem sufficiently, and I look forward to what this brain trust suggests.   Thanks again.  Jeff 

[#9]

@jsmullikenfl 

Ignore my last post.  That was a formula to return the list of distinct SOs.  Prior to that you have essentially the same solution being proposed by @GeorgieAnne ,  @Patrick2788  and myself.  To debug your formula, we would require more detailed information concerning your implementation, ideally in the form of a small workbook.

[#10]

Hey Peter, are you saying that the .png file that I attached to my last post is not sufficient? I don't know what I should have done to make it appear in with the text, as the rest of you did, but that "image001.png" is a sample of the spreadsheet. Cheers.

[#11]

@Patrick2788 

Hey Patrick,   Here's a segment of the sheet for illustration: 

So, from a different, call it "master" sheet, which contains a list of all SO#'s, I'm trying to use a single SO# as the first parameter in the XLOOKUP, or "what to look for", and using column "G" as the range to search.  Then, I'm wanting to return the values in columns "A" and "B", back to the master sheet.  But for me, as there are multiple SO#'s in each cell in column G, I'm getting errors.  I hope this clarifies the problem sufficiently, and I look forward to what this brain trust suggests.   Thanks again.  Jeff 

[#12]

@GeorgieAnne 

Hi GeorgieAnne,  

Here's a small segment of the sheet, to provide the illustration that you asked for:  

So, from a different, call it "master" sheet, which contains a list of all SO#'s, I'm trying to use a single SO# as the first parameter in the XLOOKUP, or "what to look for", and using column "G" as the range to search.  Then, I'm wanting to return the values in columns "A" and "B", back to the master sheet.  But for me, as there are multiple SO#'s in each cell in column G, I'm getting errors.  I hope this clarifies the problem sufficiently, and I look forward to what this brain trust suggests.   Thanks again.  Jeff