excelvbaexcel-formulaxlookupexcel-2021

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


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)

Example

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


Solution

  • 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: Excel2021

    enter image description here


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

    enter image description here


    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!


    enter image description here


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