Check range for current year match and return specific cell value

[redspanna]

Hi all

Simple formula needed to check year thru C4:L4 and once match found for current year the 'TOTAL' from the same year in row 23 is copied to SHEET1 Cell D3

NDA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2			FINANCIAL YEAR
3
4			2021	2022	2023
5		JAN	660	18,065
6		FEB	235	19,050
7		MAR	345	-
8		APR	1,010	-
9		MAY	430	-
10		JUN	1,665	-
11		JUL	625	-
12		AUG	1,175	-
13		SEP	1,425	-
14		OCT	140	-
15		NOV	1,800	-
16		DEC	985	-
17
18		Q1	1,240	37,115	-	-	-	-	-	-	-	-
19		Q2	3,105	-	-	-	-	-	-	-	-	-
20		Q3	3,225	-	-	-	-	-	-	-	-	-
21		Q4	2,925	-	-	-	-	-	-	-	-	-
22
23		TOTAL	10,495	37,115	-	-	-	-	-	-	-	-
Financial Year

Cell Formulas
Range	Formula
C5:C16	C5	=SUMIFS(Data!$F$2:$F$8000,Data!$B$2:$B$8000,B5,Data!$D$2:$D$8000,'Financial Year'!$C$4)
D5:D16	D5	=SUMIFS(Data!$F$2:$F$8000,Data!$B$2:$B$8000,B5,Data!$D$2:$D$8000,'Financial Year'!$D$4)
C18:L18	C18	=SUM(C5:C7)
C19:L19	C19	=SUM(C8:C10)
C20:L20	C20	=SUM(C11:C13)
C21:L21	C21	=SUM(C14:C16)
C23:L23	C23	=SUM(C5:C16)

So in this example 37,115 will be copied to SHEET1 cell D3 as the current year is 2022

Thanks in advance

 

[Eric W]

Try this in Sheet1:D3

=HLOOKUP(YEAR(TODAY()),'Financial Year'!C3:L22,20,0)

 

[redspanna]

Try this in Sheet1:D3

=HLOOKUP(YEAR(TODAY()),'Financial Year'!C3:L22,20,0)

I'm getting a #REF! error when pasted into Sheet1:D3

Also Shouldn't it be cell C4:L22 and not C3:L22?

 

[Eric W]

It works OK for me:

Book1 (version 1).xlsb
	D
3	37115
Sheet1

Cell Formulas
Range	Formula
D3	D3	=HLOOKUP(YEAR(TODAY()),'Financial Year'!C3:L22,20,0)

Is 'Financial Year' the name of the sheet? Are there any extra spaces in there? You could edit the formula, delete the 'Financial Year'!C3:L22 part, then select the other sheet, select the range, then hit Enter.

 

[redspanna]

It works OK for me:

Book1 (version 1).xlsb
	D
3	37115
Sheet1

Cell Formulas
Range	Formula
D3	D3	=HLOOKUP(YEAR(TODAY()),'Financial Year'!C3:L22,20,0)

Is 'Financial Year' the name of the sheet? Are there any extra spaces in there? You could edit the formula, delete the 'Financial Year'!C3:L22 part, then select the other sheet, select the range, then hit Enter.

Not sure what was going on, but I did as suggested and now its perfect - thanks a lot for your help

 

[Eric W]

Glad to help! Thanks for the followup. ?