Xlookup Multiple Criteria

[stevie2612]

Hi i have the below xlookup formula looking up 3 criteria.

The second criteria in bold is a country I want to have this lookup as a wildcard as the column it's looking up can have multiple different country names in the same cell.

When i add the wildcard * into the formula the result is a #N/A. Any ideas?

=XLOOKUP(1,(('Product Table Master Table'!$C$1:$C$3375=B3)*('Product Table Master Table'!$G$1:$G$3375='Test MHB'!C3)*('Product Table Master Table'!$H$1:$H$3375='Test MHB'!E3)),'Product Table Master Table'!$A$1:$A$3375,,2)



=XLOOKUP(1,(('Product Table Master Table'!$C$1:$C$3375=B3)*('Product Table Master Table'!$G$1:$G$3375="*"&'Test MHB'!C3&"*")*('Product Table Master Table'!$H$1:$H$3375='Test MHB'!E3)),'Product Table Master Table'!$A$1:$A$3375,,2)

 

[Fluff]

How about

=TAKE(FILTER('Product Table Master Table'!$A$1:$A$3375,('Product Table Master Table'!$C$1:$C$3375=B3)*(ISNUMBER(SEARCH(C3,'Product Table Master Table'!$G$1:$G$3375)))*('Product Table Master Table'!$H$1:$H$3375=E3)),1)

 

[stevie2612]

Thanks @Fluff i am getting a #CALC! error when doing that formula

 

[Alex Blakenburg]

Another option and following Fluff's assumption that the formula and criteria are on the sheet "Test MHB"

=XLOOKUP(1,
                        (
                        ('Product Table Master Table'!$C$1:$C$3375=B3)*
                        (COUNTIFS('Product Table Master Table'!$G$1:$G$3375,"*"&C3&"*")<>0)*
                        ('Product Table Master Table'!$H$1:$H$3375=E3)
                        ),
                        'Product Table Master Table'!$A$1:$A$3375,
                        "not found")

 

[Fluff]

i am getting a #CALC!

That suggests there is nothing that matches all 3 criteria. Is the formula on the "Test MHB" sheet?