Xlookup with Multiple Criteria

[acool]

Hi Everyone,

I am currently trying to create an excel formula that uses an Xlookup to provide values based on multiple criteria. I am currently trying to create an xlookup based on the criteria in column A and the Edate value in Row 1. I would like to match these up against Columns T and Columns V (Using Edate in Column V) to provide the corresponding value in Column X. I am currently trying to use the formula: =XLOOKUP(1,($A7=$T$2:$T$11)*(EDATE(B$1,0)=EDATE($V$2:$V$11,0)),$X$2:$X$11,0,0), however this is not providing me with the correct value. For example, I would like my value in B6 to equal 100, B7 to equal 200, etc. If there is no corresponding value, I would like my cells to equal 0. Any help would be greatly appreciated. Thank You!

 

[Cubist]

Try in B6 only:

=IFERROR(INDEX(X2:X11,MATCH(A6:A15&MONTH(B1:M1)&YEAR(B1:M1),T2:T11 & MONTH(V2:V11) & YEAR(V2:V11),0)),"")

XLOOKUP option

=XLOOKUP(B6&MONTH($B$1:$M$1)&YEAR($B$1:$M$1),$T$2:$T11&MONTH($V$2:$V$11)&YEAR($V$2:$V$11),$X$2:$X$11,"")

 

[acool]

Try in B6 only:

=IFERROR(INDEX(X2:X11,MATCH(A6:A15&MONTH(B1:M1)&YEAR(B1:M1),T2:T11 & MONTH(V2:V11) & YEAR(V2:V11),0)),"")

XLOOKUP option

=XLOOKUP(B6&MONTH($B$1:$M$1)&YEAR($B$1:$M$1),$T$2:$T11&MONTH($V$2:$V$11)&YEAR($V$2:$V$11),$X$2:$X$11,"")

The xlookup option worked! Thank You!

 

[acool]

@Cubist if you don't mind me asking on other question...I am struggling with an additional aspect of this. I am also trying to match the value in column A and the dates in Row B:M to the corresponding value in column AB. However, I would like to carry this number out for the remainder of the months, as opposed to one single month. For example, for Cell E6 I would like the value to be $417 and have this carry out for the remainder of the months, Cell E7 to be $833 and carry out the rest of the months etc. Is there any way to work that into the formula that you created above. Thank you again for all of your help! Very much appreciated.

 

[Cubist]

Do you mean like this? To make things a little easier. Have your header row 3 end of the month and column AB at the end of the month.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N							U						AA	AB
1
2		3/31/24
3		3/31/24	4/30/24	5/31/24	6/30/24	7/31/24	8/31/24	9/30/24	10/31/24	11/30/24	12/31/24	1/31/25	2/28/25
4
5																					A						417	6/30/24
6	A				417	417	417	417	417	417	417	417	417								B						833	6/30/24
7	B				833	833	833	833	833	833	833	833	833								C						1250	7/31/24
8	C					1250	1250	1250	1250	1250	1250	1250	1250								D						1667	8/31/24
9	D						1667	1667	1667	1667	1667	1667	1667								E						2083	9/30/24
10	E							2083	2083	2083	2083	2083	2083								F						2500	10/31/24
11	F								2500	2500	2500	2500	2500								G						2917	11/30/24
12	G									2917	2917	2917	2917								H						3333	12/31/24
13	H										3333	3333	3333								I						3750	1/31/25
14	I											3750	3750								J						4167	3/31/25
15	J
Sheet7

Cell Formulas
Range	Formula
B3:M3	B3	=EDATE(B2,SEQUENCE(1,12,0))
B6:M15	B6	=LET(f,FILTER($AA$5:$AB$14,$U$5:$U$14=A6),IF($B$3#>=(CHOOSECOLS(f,2)),CHOOSECOLS(f,1),""))
Dynamic array formulas.

 

[acool]

@Cubist Yes, this works thank you!