Horizontal Lookup on Multiple Rows

N

Naru2

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
  • Solved
Lookup formula not working if pointed to cells with formulas
Replies
4
Views
163
Fluff
Fluff
M
  • Solved
Formula to 2-way look up a unique identifier and a date, then take the highest number from another column
Replies
3
Views
147
MrsFraser07
M
C
  • Solved
Lookup certain value in row and give back cell value in same column first row
Replies
7
Views
417
Fluff
Fluff
L
  • Question
Formula matching multiple variables to bring back one quantity value
Replies
3
Views
203
lsdientz
L
K
  • Solved
last occurrence of a value in a 4 column array
Replies
4
Views
188
kmr47
K

Forum statistics

Threads
1,216,075
Messages
6,128,667
Members
449,462
Latest member
Chislobog

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