Using Xmatch to look at multiple columns to extract list?

[Coyotex3]

Hey all, so in theory this seems doable, but I can't figure out. I have a range with some data from which I'd like to compare two columns in a range to two separate columns in another range and extract result based on the matches. I'm able to use a combination of Filter, ISNUMBER, and Xmatch to extract results when only looking at 1 column but can't do it with 2.


I'm looking at a range of data similar to this one:

Book1
	A	B	C
1	Name	City	Amount
2	John	NYC	100
3	Payne	MA	200
4	Austin	UT	300
5	John	NC	400
6	Payne	NJ	400
7	Brandon	CA	200
8	Ty	CO	500
9	Brandon	NE	100
10	John	WA	100
11	Autin	ID	500
Sheet1

I'd like to compare that range to this list:

Book1
	J	K
1	Name	City
2	John	NYC
3	Payne	MA
4	Austin	UT
5	Brandon	CA
6	Ty	CO
7	John	WA
8	Autin	ID
Sheet1

To get these results:

Book1
	F	G	H
1	Name	City	Amount
2	John	NYC	100
3	Payne	MA	200
4	Austin	UT	300
5	Brandon	CA	200
6	Ty	CO	500
7	John	WA	100
8	Autin	ID	500
Sheet1

Can this be accomplished with a single cell formula?

 

[hagia_sofia]

How about:

=XLOOKUP(J2:J8&K2:K8,A2:A11&B2:B11,C2:C11)

 

[Coyotex3]

Hey! Thanks for the response, This Xlookup formula might work. Just have to test it out on the actual data set.

Curious to know if Xmatch could also handle something like this.

 

[Cubist]

XMATCH alone doesn't return the value, only the index. You'd need to use it with something like INDEX.

Book2
	A	B	C	D	E	F	G
1	Name	City	Amount		Name	City	Amount
2	John	NYC	100		John	NYC	100
3	Payne	MA	200		Payne	MA	200
4	Austin	UT	300		Austin	UT	300
5	John	NC	400		Brandon	CA	200
6	Payne	NJ	400		Ty	CO	500
7	Brandon	CA	200		John	WA	100
8	Ty	CO	500		Autin	ID	500
9	Brandon	NE	100
10	John	WA	100
11	Autin	ID	500
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=INDEX(C2:C11,XMATCH(E2:E8&"|"&F2:F8,A2:A11&"|"&B2:B11))
Dynamic array formulas.

 

[hagia_sofia]

Another option:

=INDEX(C2:C11,MAP(F2:F8,G2:G8,LAMBDA(a,b,XMATCH(1,(a=A2:A11)*(b=B2:B11)))),0)

 

[Coyotex3]

Thank you both for the options given to me!

I ended up going the Xlookup route as this formula will be housed in a shared file and it will be easier for most users to understand & Troubleshoot.