How does XLOOKUP do the lookup based on two criteria?

[OaklandJim]

Team Excel

Somewhere on the web I found syntax for using XLOOKUP to do the lookup based on two lookup criteria. But I'd like to understsand how it works. This XLOOKUP formula looks up quantity for a specified stock symbol and for a specific account. (Two accounts have the same stock.) I'd like to know how this works.

XLOOKUP(1,(Summary!Symbols=rrSymbol)*(Summary!AccountNumbers=rrAccountNumber),Summary!Quantities)

(rr signifies "relative row" addressing.)

I appreciate your help.

 

[StephenCrump]

Does this help?

	A	B	C	D	E	F	G	H	I
1						In detail ...
2		Stock	Account	Quantity		Stock match?	Account match?	Both match?	Quantity
3		A	1	2		FALSE	FALSE	0	2
4		B	1	3		TRUE	FALSE	0	3
5		A	2	5		FALSE	TRUE	0	5
6		B	2	7		TRUE	TRUE	1	7
7		C	1	11		FALSE	FALSE	0	11
8		D	1	13		FALSE	FALSE	0	13
9		A	3	17		FALSE	FALSE	0	17
10		D	2	19		FALSE	TRUE	0	19
11
12		Lookup
13		B	2	7
14				7
15
Sheet1

Cell Formulas
Range	Formula
F3:G10	F3	=B3:B10=B13
H3:H10	H3	=F3#*G3#
I3:I10	I3	=D3:D10
D13	D13	=XLOOKUP(1,(B3:B10=B13)*(C3:C10=C13),D3:D10)
D14	D14	=XLOOKUP(1,H3#,I3:I10)
Dynamic array formulas.

Note that XLOOKUP will find only the first occurrence. If there is a possibility of multiple matches, you'd use FILTER instead.

 

[Eric W]

Go to the Formulas tab and look for the Evaluate Formula button. Select the cell with the formula, then click Evaluate Formula, and you can step through it to see how it works.

But for your formula, the basic idea is that the (Summary!Symbols=rrSymbol) part creates an array of TRUE/FALSE values, so the array has as many entries as Summary!Symbols does, for example {TRUE,TRUE,FALSE,FALSE, . . .}. Then the (Summary!AccountNumbers=rrAccountNumber) part creates another similar array of the same size, {FALSE,TRUE,FALSE,FALSE, . . .}. Then the 2 arrays are multiplied together, item by item. And when multiplying Boolean values, they're converted to 1s and 0s, so those 2 arrays multiplied together become {0,1,0,0, . . .}. Then the XLOOKUP looks for a 1 in that array, and returns the value from Summary!Quantities in the same position.

Hope this helps!