Embedding Xlookup into IF

Good afternoon all,

I am looking to use Xlookup in an If statement with and, but I'm struggling.

Data is below in columns A, B, C. I want to loop this and search a concatenated A&B, returning C, and if A&B equal 1 and 2, return a value. However, I don't think Xlookup is what I want necessarily, but am not wanting to give up on it yet.


A M022C58659 1
B M022C58659 1
C M022C58659 1
D M022C58659 1
E M022C58659 1
A M022C58659 2
B M022C58659 2
C M022C58659 2
D M022C58659 2
E M022C58659 2

=IF(AND((XLOOKUP(A2&B2, A:A&B:B, C:C)=1), (XLOOKUP(A2&B2, A:A&B:B, C:C)=2)), "Y")

Trying to do this without moving to VB as I'm very rusty with VB.

Thank you for any help.

Sorry about that. As I was searching, I noticed the banner.

Column E shows my desired output.

Sorry for the confusion and yes, didn't help with the data being "A, B....."

Thank you. I believe I was overcomplicating it seeking a lookup solution. Per Richard's comment below, my data is more complex, but I was using a simplified version to troubleshoot.

If your data could have duplicates then this would work if the values of column C are always 1 or 2:

=IF(COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2,$C$2:$C$9,1)>0,IF(COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2,$C$2:$C$9,2)>0,"BOTH",1),2)

Thank you. Is a XLOOKUP route achievable in something like this? Just looking for another way to skin a cat, not that the countif embedded into the if isn't viable.

XLOOKUP will always return the first match, so if you wanted to use it then you would need to build in the 1 and 2 in column C. Loosely following your original attempt, this would give your expected results:

=IF(AND((XLOOKUP(A2&B2&1, A:A&B:B&C:C, C:C, 0)=1), (XLOOKUP(A2&B2&2, A:A&B:B&C:C, C:C, 0)=2)), "BOTH", XLOOKUP(A2&B2, A:A&B:B, C:C))

Note I have added 0 as the "if not found" parameter for XLOOKUP, without this it will not work as it returns an error if the value is not found. However I would say XLOOKUP is not the best function to use if you are just checking whether something exists.

Another alternative would be to use MATCH:

=IF(ISNUMBER(MATCH(A2&B2&1,A:A&B:B&C:C,0)),IF(ISNUMBER(MATCH(A2&B2&2,A:A&B:B&C:C,0)),"BOTH",1),2)

I really appreciate this explanation. It is very helpful. Thanks again.