Horizontal Lookup on Multiple Rows

[Naru2]

I have a spreadsheet with several years and values working down the page. I'm trying to find a formula where I can lookup a date to the data set, but the date is not always going to be in that first row, it could be several rows down. Then I need the corresponding number below it. So, find the date amongst several rows and then return the value that is 1 row below that. Thanks!

 

[awoohaw]

is the date you seek always the first of the month? If the date is the 27th of the month, do you want that month, or the next month?

 

[Naru2]

is the date you seek always the first of the month? If the date is the 27th of the month, do you want that month, or the next month?

The dates are formatted to always be 1st of the month, so 1/1/2025, 2/1/2025, etc.

 

[awoohaw]

And are the numbers adjacent to the dates less than the minimum date value or greater than the maximum date value?

 

[awoohaw]

maybe this will work for you:

this could also work too, if the months are all consecutive as

=INDEX($A$3:$L$100,MATCH(YEAR(P3),YEAR($A$3:$A$100),0)+1,MONTH($P$3))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3	2025-01	2025-02	2025-03	2025-04	2025-05	2025-06	2025-07	2025-08	2025-09	2025-10	2025-11	2025-12			Date to Find	2026-05-01
4	31	48	45	28	36	35	46	31	40	42	47	45			Result	29
5
6
7	2026-01	2026-02	2026-03	2026-04	2026-05	2026-06	2026-07	2026-08	2026-09	2026-10	2026-11	2026-12
8	28	31	35	26	29	12	44	33	25	42	11	22
9
10
Sheet2

Cell Formulas
Range	Formula
P4	P4	=INDEX($A$3:$L$100,MATCH(YEAR(P3),YEAR($A$3:$A$100),0)+1,MATCH(MONTH($P$3),MONTH($A$3:$L$3),0))

 

[Naru2]

And are the numbers adjacent to the dates less than the minimum date value or greater than the maximum date value?

I'm not sure exactly what you're asking, but the dates are going to be anything between 2024 and 2034. The number values below the dates are units, so could be in the 10's, 100's or 1,000's.

 

[awoohaw]

I'm not sure exactly what you're asking, but the dates are going to be anything between 2024 and 2034. The number values below the dates are units, so could be in the 10's, 100's or 1,000's.

dates are stored as numbers, as long as your numbers are not between
45292 and 49279 you should be good.

 

[Scott R]

=SUMIF(A1:L5, "12/1/26", A2:L6)

 

[Naru2]

maybe this will work for you:

this could also work too, if the months are all consecutive as

=INDEX($A$3:$L$100,MATCH(YEAR(P3),YEAR($A$3:$A$100),0)+1,MONTH($P$3))

The dates are not always consecutive, and the values are not always directly underneath. The value could be 9 or so rows below it.

 

[Cubist]

Which Excel version are you using?