XLOOKUP with a second condition

Question

I have a table like the following:

In column "F", I would like to have the text from Column "C", however, only if "Condition" from column "B" is "CondB".

If I use the formula written in "F2" - "=XLOOKUP(E2; A:A; C:C)", it always just takes the first matching cell, i.e. in "F2" it writes "TextA", but I would like to have "TextB" written there.

Is there a way to add a second condition to the XLOOKUP formula? Unfortunately, I cannot use VBA.

Answer 1

I use INDEX MATCH instead with & Operator to concatenate Col A & B.

If CondB is constant, then you can try the following. In F2

=INDEX($C$2:$C$5,MATCH(E2&"CondB",$A$2:$A$5&$B$2:$B$5,0))

Same XLOOKUP Formula can also be used as

=XLOOKUP(E2&"CondB",A:A&B:B,C:C)

However using entire column reference may slow down a bit.