Forum Discussion
XLOOKUP Challenge
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
Hellojsmullikenfl
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!
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