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