Xlookup Vertical & horizontal with 3 criteria

[kitsa]

Hi,
I'm trying to get figures using Xlookup, but have got stuck with 3 criteria in vertical and horizontal:
E.g. Code - AA1, Start Date (20/01/23), End Date (23/01/23) = Sum (40), as per pic

Can someone help?

Thanks in advance

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, for the future (to save helpers having to manually type sample data & therefore to get more helpers and faster results ):
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

You cannot get those results with XLOOKUP, but you can with SUMIFS (I have assumed that you made a mistake with the row 3 result)

23 02 28.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Start	End	Total			20/01/2023	21/01/2023	22/01/2023	23/01/2023	24/01/2023	25/01/2023	26/01/2023	27/01/2023
2	20/01/2023	23/01/2023	40			10	10	10	10	10	10	10	10
3	21/01/2023	24/01/2023	80			20	20	20	20	20	20	20	20
4	24/01/2023	27/01/2023	120			30	30	30	30	30	30	30	30
SUMIFS

Cell Formulas
Range	Formula
D2:D4	D2	=SUMIFS(G2:N2,G$1:N$1,">="&B2,G$1:N$1,"<="&C2)

 

[kitsa]

Hi Peter,
Thanks for the info. I have updated my details now to support. I'm unsure how to do the XL2BB add-in, your information is too complex to me, but I'm sure I will work it out.
With your code it works for to criteria dates, but I need to match code (A:A) and (F:F) first, then the two dates to get the figures.

 

[Peter_SSs]

I have updated my details

Great, thanks for that.

but I need to match code (A:A) and (F:F) first, then the two dates to get the figures.

Then I think that you need to give us a better representative set of sample data and expected results.

I'm unsure how to do the XL2BB add-in

Try to carefully follow through the steps outlined in the link provided. Use the Test Here forum to try it out.