Xlookup for 2 criteria to search

[Eric Tsang]

Dear Guru

I am trying to solve an issue

Product Shop 1 Shop2
Judge ABC
PCRC ZZZ

To locate the product either in Shop 1 or Shop 2

I tried the Xlookup but seems not working


Appreciated your help

Eric

[kvsrinivasamurthy]

Formula is available in G8.
I am unable to upload the formula.

[AliGW]

You can only look up one column at a time. Try this instead:

=XLOOKUP(H8,C7:C8,B7:B8,XLOOKUP(H8,D7:D8,B7:B8,"",0,1),0,1)

[Glenn Kennedy]

Non-array formula, expandable to any number of "Shops".

=IFERROR(INDEX($6:$6,,AGGREGATE(15,6,COLUMN($C$7:$D$8)/($C$7:$D$8=$H$8),1)),"")

[josephteh]

Modifying from Ali's formula, =XLOOKUP(H8,C7:C8,TRANSPOSE(C6:D6),XLOOKUP(H8,D7:D8,TRANSPOSE(C6:D6),"",0,1),0,1)

[josephteh]

Modifying from Ali's formula, =XLOOKUP(H8,C7:C8,TRANSPOSE(C6:D6),XLOOKUP(H8,D7:D8,TRANSPOSE(C6:D6),"",0,1),0,1)

Apparently, it's not working!

[Eric Tsang]

Dear Ali

Thanks so much for your reply

So how, after reading yours formula, I test this :=XLOOKUP(H8,C7:C9,B7:B9,XLOOKUP(H8,D7:D9,B7:B9),,1)

It works as what I want.

the case is, I have a registration form, that all user to input either Chinese name or English name ( column B or Column C)
So I have to test/match their name to return the "Column B"

Thanks for everyone here

Eric

[Glenn Kennedy]

??? What is your expected answer???

[Eric Tsang]

Glenn

The formula that i made is =XLOOKUP(H8,C7:C9,B7:B9,XLOOKUP(H8,D7:D9,B7:B9),,1)

Your is good to, but just i need the return from Column "A"

Thanks anyway

Eric

[Glenn Kennedy]

I read this:

T"o locate the product either in Shop 1 or Shop 2"

in your first post as meaning you wanted the shop name returned. For completeness:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($C$7:$D$8)/($C$7:$D$8=$H$8),1)),"")

[Eric Tsang]

Glenn

Thanks

Eric