Horizontal Lookup on Multiple Rows

N

Naru2

New Member
Joined
Apr 9, 2014
Messages
41
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!

1714394088055.png
 
Sort by date Sort by votes

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try @Scott R suggestion. It's simple.

Book1
ABCDEFGHIJKL
11/1/252/1/253/1/254/1/255/1/256/1/257/1/258/1/259/1/2510/1/2511/1/2512/1/25
2314845283635463140424745
3
4
51/1/262/1/263/1/264/1/265/1/266/1/267/1/268/1/269/1/2610/1/2611/1/2612/1/26
6283135262912443325421122
7
8
9
10Date to Find12/1/26
11Result22
Sheet3
Cell Formulas
RangeFormula
B11B11=SUMIF(A1:L5, B10, A2:L6)
 
Upvote 0
Naru2 said:
The dates are not always consecutive, and the values are not always directly underneath. The value could be 9 or so rows below it.
Click to expand...
okay, try this then:

Book1
ABCDEFGHIJKLMNOP
1
2
32025-012025-022025-032025-042025-052025-062025-072025-082025-092025-102025-112025-12Date to Find2026-05-05
4314845283635463140424745Result29
5
6
72026-012026-022026-032026-042026-052026-062026-072026-082026-092026-102026-112026-12
8283135262912443325421122
Sheet2
Cell Formulas
RangeFormula
P4P4=LET(y,INDEX($A$3:$L$100,MATCH(YEAR(P3),YEAR($A$3:$A$100),0),0),d,INDEX($A$3:$L$100,MATCH(YEAR(P3),YEAR($A$3:$A$100),0)+1,0),INDEX(d,MATCH(MONTH(P3),MONTH(y),0)))
 
Upvote 0
This isn't working and I'm not sure if it's because the months and year are not consecutive across the row. Also, it will return the value directly below it, but i'm not sure how to tell it grab a number several rows down. So I am pasting the data in the way that it is actually in the sheet I am trying to use (I was trying to simplify it earlier, thinking it would be transferable).

So you can see the row starts with 2025-01, but partway through it switches to 2024-09. Then, I want to grab the row that says "Shipments" to the left, not the row directly below. So I want the number to show as 737, not 3,105. The reason I want the lookup is to put a whole year in the right order in one row. I hope this helps!

2025-012025-022025-032025-042025-052025-062024-072024-082024-092024-102024-112024-12
3,105​
3,670​
4,159​
3,091​
2,687​
3,352​
2,068​
2,544​
3,467​
2,704​
3,264​
5,041​
Total3,1053,6704,1593,0912,6873,3522,0682,5443,4672,7043,2645,041
Shipments7378729887346387962466048236427751,197
Total7378729887346387962466048236427751,197
2026-012026-022026-032026-042026-052026-062025-072025-082025-092025-102025-112025-12
3,105​
3,670​
4,159​
3,091​
2,687​
3,352​
2,068​
2,544​
3,467​
2,704​
3,264​
5,041​
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​
Shipments7378729887346387964916048236427751,197
Total7378729887346387964916048236427751,197
 
Upvote 0
Are the spacing between the rows always the same?
 
Upvote 0
Ok, try this. This assumes your data starts in Column A. If it's not, some adjustments are needed.
Edit: I realized I didn't take into account when there are more rows below.
Book1
ABCDEFGHIJKLMN
12025-012025-022025-032025-042025-052025-062024-072024-082024-092024-102024-112024-12
23,1053,6704,1593,0912,6873,3522,0682,5443,4672,7043,2645,041
3Total310536704159309126873352206825443467270432645041
4
5
6Shipments7378729887346387962466048236427751197
7Total7378729887346387962466048236427751197
8
9
10
11
122026-012026-022026-032026-042026-052026-062025-072025-082025-092025-102025-112025-12
133,1053,6704,1593,0912,6873,3522,0682,5443,4672,7043,2645,041
14Total3,1053,6704,1593,0912,6873,3522,0682,5443,4672,7043,2645,041
15
16
17Shipments7378729887346387964916048236427751197
18Total7378729887346387964916048236427751197
19
20
21
22Date to find2026-04
23Result734
Sheet4
Cell Formulas
RangeFormula
B22B22="2026-04"
B23B23=LET( r,MIN(IF(B22=A1:N18,ROW(A1:N18))), c,MIN(IF(B22=A1:N18,COLUMN(A1:N18))), d,DROP(A1:N18,r), f,TAKE(FILTER(d,CHOOSECOLS(d,1)="Shipments"),1), INDEX(f,,c))
 
Last edited:
Upvote 0
Cubist said:
Ok, try this. This assumes your data starts in Column A. If it's not, some adjustments are needed.
Edit: I realized I didn't take into account when there are more rows below.
Book1
ABCDEFGHIJKLMN
12025-012025-022025-032025-042025-052025-062024-072024-082024-092024-102024-112024-12
23,1053,6704,1593,0912,6873,3522,0682,5443,4672,7043,2645,041
3Total310536704159309126873352206825443467270432645041
4
5
6Shipments7378729887346387962466048236427751197
7Total7378729887346387962466048236427751197
8
9
10
11
122026-012026-022026-032026-042026-052026-062025-072025-082025-092025-102025-112025-12
133,1053,6704,1593,0912,6873,3522,0682,5443,4672,7043,2645,041
14Total3,1053,6704,1593,0912,6873,3522,0682,5443,4672,7043,2645,041
15
16
17Shipments7378729887346387964916048236427751197
18Total7378729887346387964916048236427751197
19
20
21
22Date to find2026-04
23Result734
Sheet4
Cell Formulas
RangeFormula
B22B22="2026-04"
B23B23=LET( r,MIN(IF(B22=A1:N18,ROW(A1:N18))), c,MIN(IF(B22=A1:N18,COLUMN(A1:N18))), d,DROP(A1:N18,r), f,TAKE(FILTER(d,CHOOSECOLS(d,1)="Shipments"),1), INDEX(f,,c))
Click to expand...
I get a #CALC! error.
 
Upvote 0
Naru2 said:
I get a #CALC! error.
Click to expand...
Can you use XL2BB to post the sample data? There are few things that could cause that. Difficult to diagnose when we can't see what you're doing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Lookup 2/1 works on one line and not on others
Replies
5
Views
124
Fluff
Fluff
E
  • Solved
Lookup formula not working if pointed to cells with formulas
Replies
4
Views
327
Fluff
Fluff
D
  • Question Question
Static Timestamp
Replies
4
Views
949
Fluff
Fluff
sparky2205
  • Solved
Absolute reference from a lookup
2
Replies
17
Views
709
Alex Blakenburg
A
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
137
Ludwig
L

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top