I'm essentially trying to lookup multiple items located in the data table in column 2 and return the corresponding order number from column 1 IF all the items match.
I was thinking I may have to utilise the xlookup function, and a few IFs, in conjunction with other functions - or it may only be achievable via VBA?
Any tips or advice would be greatly appreciated as I'm really not sure how to begin this one or what search terms to enter to try to find solutions and/or adivce.
Please let me know if I can provide anymore info to assist with this query.
(Version: Office 2021)
Markdown version of data table:
Order No. | Item | Qty | Order Date |
---|---|---|---|
001 | Apple | 2 | 30/01/2024 |
001 | Banana | 1 | 30/01/2024 |
001 | Pear | 1 | 30/01/2024 |
002 | Cherries | 4 | 02/02/2024 |
003 | Banana | 2 | 08/02/2024 |
004 | Apple | 3 | 15/02/2024 |
004 | Cherries | 5 | 15/02/2024 |
005 | Pear | 2 | 24/02/2024 |
Many thanks
May be there is more elegant way of doing this perhaps using a helper column to achieve the desired output for the version of Excel as mentioned in the OP: Excel
2021
• Formula used in cell G3
=UNIQUE(FILTER(A3:A10,TEXTJOIN("|",,INDEX(F3:F10,SEQUENCE(ROWS(F3:F10))))=E3:E10,""))
While the helper column formula is:
=TEXTJOIN("|",,FILTER(B$3:B$10,A$3:A$10=$A3))
Edit: 3/1/2024
As per new comments of OP:
I’ll be sure to study these functions as it will be useful for future projects :). As an optional extra, do you know of a way I can make it search for the items in no particular order? i.e. so entering “Banana, Pear and Apple” still brings up order 1? Currently I would have to enter “Apple, Banana & Pear” in that particular order as that’s the order they are in the table. I hope I have explained that correctly!
Change in both of the formulas to ensure it work accordingly as per the given conditions. Additionally, it will also exclude duplicates for same order, if may arise.
• For Helper:
=TEXTJOIN("|",,SORT(UNIQUE(FILTER(B$3:B$10,A$3:A$10=$A3,""))))
• For Matching Order:
=UNIQUE(FILTER(A3:A10,TEXTJOIN("|",,SORT(UNIQUE(INDEX(F3:F10,SEQUENCE(ROWS(F3:F10))))))=E3:E10,""))