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]

if you can't use the xl2bb then I suggest sanitizing your data for privacy and posting a copy of the file on Drop Box or some other sharing site.

But, regarding your data report itself? How did it get in this format? Do you have a source data file that you can use instead of this report that is very difficult to get other reporting out of?

 

[Naru2]

Here is a link. The data is pasted in as full years to start, but then it's manipulated and reconfigured. I need the adjusted #'s to be put back into a normal calendar year. I thought there would be an easy way to look it up and straighten it out, but seems a bit more complicated.

Upgrade.png | Powered by Box

cloud.box.com

 

[awoohaw]

Here is a link. The data is pasted in as full years to start, but then it's manipulated and reconfigured. I need the adjusted #'s to be put back into a normal calendar year. I thought there would be an easy way to look it up and straighten it out, but seems a bit more complicated.

Upgrade.png | Powered by Box

cloud.box.com

it does not show up there as an excel file. i just get a .png image placeholder. not even a picture of a worksheet.

 

[Naru2]

Ugh. This one?

sample data.xlsx | Powered by Box

app.box.com

 

[Cubist]

1) Your ranges weren't right
2) Your "Shipments" had spaces. (There's an increase indent option under the Alignment on the Home tab).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2			7	8	9	10	11	12	1	2	3	4	5	6
3		Year 1	2025-01	2025-02	2025-03	2025-04	2025-05	2025-06	2024-07	2024-08	2024-09	2024-10	2024-11	2024-12
4	Total	5g	3,105	3,670	4,159	3,091	2,687	3,352	2,068	2,544	3,467	2,704	3,264	5,041	39,152
5		Total	3,105	3,670	4,159	3,091	2,687	3,352	2,068	2,544	3,467	2,704	3,264	5,041	39,152
6
7			95%	95%	95%	95%	95%	95%	95%	95%	95%	95%	95%	95%
8
9		5g	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
10
11	Units	5g	737	872	988	734	638	796	491	604	823	642	775	1,197	9,299
12		Total	737	872	988	734	638	796	491	604	823	642	775	1,197	9,299
13
14	Pipeline Fill	5g
15
16	Replenishment		100%	100%	100%	100%	100%	100%	50%	100%	100%	100%	100%	100%
17
18	Shipments	5g	737	872	988	734	638	796	246	604	823	642	775	1,197	9,053
19		Total	737	872	988	734	638	796	246	604	823	642	775	1,197	9,053
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36		Year 2	2026-01	2026-02	2026-03	2026-04	2026-05	2026-06	2025-07	2025-08	2025-09	2025-10	2025-11	2025-12
37	Total	5g	3,105	3,670	4,159	3,091	2,687	3,352	2,068	2,544	3,467	2,704	3,264	5,041	39,152
38		Total	3,105	3,670	4,159	3,091	2,687	3,352	2,068	2,544	3,467	2,704	3,264	5,041	39,152
39
40			95%	95%	95%	95%	95%	95%	95%	95%	95%	95%	95%	95%
41
42		5g	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%	25%
43
44	Units	5g	737	872	988	734	638	796	491	604	823	642	775	1,197	9,299
45		Total	737	872	988	734	638	796	491	604	823	642	775	1,197	9,299
46
47	Replenishment		100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%
48
49	Shipments	5g	737	872	988	734	638	796	491	604	823	642	775	1,197	9,299
50		Total	737	872	988	734	638	796	491	604	823	642	775	1,197	9,299
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
69		2025	737	872	988	734	638	796	491	604	823	642	775	1197
70		2026	737	872	988	734	638	796
71
72		LOOKUP:	2025-01	2025-02	2025-03	2025-04	2025-05	2025-06	2025-07	2025-08	2025-09	2025-10	2025-11	2025-12
73			2026-01	2026-02	2026-03	2026-04	2026-05	2026-06	2026-07	2026-08	2026-09	2026-10	2026-11	2026-12
Sheet1

Cell Formulas
Range	Formula
C69:N69,C70:H70	C69	=LET( r,MIN(IF(C72=$A$1:$O$65,ROW($A$1:$O$65))), c,MIN(IF(C72=$A$1:$O$65,COLUMN($A$1:$O$65))), d,DROP($A$1:$O$65,r), f,FILTER(d,TRIM(CHOOSECOLS(d,1))="Shipments"), INDEX(f,1,c))

 

[Naru2]

I didn't even notice that! As soon as I fixed that all the formulas populated. This is pretty cool! Thank you so much for sticking it out with me.

 

[Fluff]

Microsoft 365

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’)

 

[Naru2]

When I took the spacing out of the word "Shipments", it fixed the formula for one of the years, but not the other. Now I get a "SPILL" error.

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2025 #SPILL! #SPILL! #SPILL! #SPILL! #SPILL! #SPILL! 491 604 823 642 775 1,197
2026 737 872 988 734 638 796

 

[Cubist]

It's because those dates are not in the data. I've added error handler when that happens.

=LET(
r,MIN(IF(C72=$A$1:$O$65,ROW($A$1:$O$65))),
c,MIN(IF(C72=$A$1:$O$65,COLUMN($A$1:$O$65))),
d,DROP($A$1:$O$65,r),
f,FILTER(d,TRIM(CHOOSECOLS(d,1))="Shipments"),
IF(r=0,"",INDEX(f,1,c)))

 

[Naru2]

The 2025 data starts in row C3, so there should be values returned. When you remove the spacing from "Shipments", then the error shows up, which is strange.