Lookup multiple criteria from column 2 in a table and return the value in column 1 if all match

Question

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

Answer 1

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²⁰²¹

---

• 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,""))

---