Using INDEX/MATCH across multiple concatenated tables

T

travellerva

Board Regular
Joined
Mar 31, 2012
Messages
52
Office Version
  1. 365
Platform
  1. MacOS
I have a table of historical stock quotes for multiple stock symbols and need to find the correct formula for selecting the quote for a specific date for a specific symbol.

Book1
ABCDEFGH
1AAPLAZNBRK.BBRSC
2DateCloseDateCloseDateCloseDateClose
32/14/25$ 244.602/14/25$ 73.582/14/25$ 479.592/17/251,318.00
42/13/25$ 241.532/13/25$ 74.452/13/25$ 480.492/14/251,320.00
52/12/25$ 236.872/12/25$ 74.432/12/25$ 471.732/13/251,328.00
62/11/25$ 232.622/11/25$ 72.732/11/25$ 472.612/12/251,332.00
72/10/25$ 227.652/10/25$ 72.662/10/25$ 470.172/11/251,340.00
82/7/25$ 227.632/7/25$ 71.992/7/25$ 472.742/10/251,344.00
92/6/25$ 233.222/6/25$ 72.362/6/25$ 477.222/7/251,346.00
102/5/25$ 232.472/5/25$ 70.942/5/25$ 473.732/6/251,358.00
112/4/25$ 232.802/4/25$ 68.962/4/25$ 467.492/5/251,344.00
122/3/25$ 228.012/3/25$ 69.862/3/25$ 464.612/4/251,340.00
131/31/25$ 236.001/31/25$ 70.761/31/25$ 468.672/3/251,350.00
141/30/25$ 237.591/30/25$ 71.241/30/25$ 472.351/31/251,364.00
151/29/25$ 239.361/29/25$ 70.251/29/25$ 469.261/30/251,356.00
161/28/25$ 238.261/28/25$ 69.591/28/25$ 469.971/29/251,346.00
171/27/25$ 229.861/27/25$ 70.081/27/25$ 474.701/28/251,348.00
181/24/25$ 222.781/24/25$ 69.061/24/25$ 463.191/27/251,330.00
191/23/25$ 223.661/23/25$ 68.601/23/25$ 459.831/24/251,340.00
201/22/25$ 223.831/22/25$ 68.201/22/25$ 460.511/23/251,340.00
211/21/25$ 222.641/21/25$ 67.961/21/25$ 468.571/22/251,344.00
221/17/25$ 229.981/17/25$ 66.601/17/25$ 467.951/21/251,352.00
Sheet1


I need to find the correct formula for, say, finding the quote for BRSC on 2/3/25 (highlighted on the above minisheet. HAve tried various versions of INDEX/MATCH without success. There must be a simple answer! If it helps, I could shift the header row (1) to the left so the symbol header is above the date range.

Thanks for any help.
 
Sort by date Sort by votes
Please try this

Book1
ABCDEFGHIJKLM
1AAPLAZNBRK.BBRSC
2DateCloseDateCloseDateCloseDateClose2/3/20252/4/2025
32/14/2025244.62/14/202573.582/14/2025479.592/17/20251318AAPL228.01232.80
42/13/2025241.532/13/202574.452/13/2025480.492/14/20251320AZN69.8668.96
52/12/2025236.872/12/202574.432/12/2025471.732/13/20251328BRK.B464.61467.49
62/11/2025232.622/11/202572.732/11/2025472.612/12/20251332BRSC1,350.001,340.00
72/10/2025227.652/10/202572.662/10/2025470.172/11/20251340
82/7/2025227.632/7/202571.992/7/2025472.742/10/20251344
92/6/2025233.222/6/202572.362/6/2025477.222/7/20251346
102/5/2025232.472/5/202570.9352/5/2025473.732/6/20251358
112/4/2025232.82/4/202568.962/4/2025467.492/5/20251344
122/3/2025228.012/3/202569.862/3/2025464.612/4/20251340
131/31/20252361/31/202570.761/31/2025468.672/3/20251350
141/30/2025237.591/30/202571.241/30/2025472.351/31/20251364
151/29/2025239.361/29/202570.251/29/2025469.261/30/20251356
161/28/2025238.261/28/202569.591/28/2025469.971/29/20251346
171/27/2025229.861/27/202570.081/27/2025474.71/28/20251348
181/24/2025222.781/24/202569.061/24/2025463.191/27/20251330
191/23/2025223.661/23/202568.61/23/2025459.831/24/20251340
201/22/2025223.831/22/202568.21/22/2025460.511/23/20251340
211/21/2025222.641/21/202567.961/21/2025468.571/22/20251344
221/17/2025229.981/17/202566.61/17/2025467.951/21/20251352
Sheet3
Cell Formulas
RangeFormula
L3:M6L3=LET(R,MATCH(L$2,INDEX($A$3:$H$22,,MATCH($K3,$A$1:$H$1,0)),0),INDEX($A$3:$H$22,R,MATCH($K3,$A$1:$H$1,0)+1))
 
Upvote 0
Jeffrey Mahoney said:
=LET(R,MATCH(L$2,INDEX($A$3:$H$22,,MATCH($K3,$A$1:$H$1,0)),0),INDEX($A$3:$H$22,R,MATCH($K3,$A$1:$H$1,0)+1))
Click to expand...
Thanks, Jeffrey. I had been wrestling with a similar approach before posting on the forum but didn't get that far. I can see how your solution should work but it doesn't on my system (Excel for Mac Version 16.95.1 (25031528).
This is what I get:
Book1
ABCDEFGHIJKLM
1AAPLAZNBRK.BBRSC
2DateCloseDateCloseDateCloseDateClose2/3/252/4/25
32/14/25$ 244.602/14/25$ 73.582/14/25$ 479.592/17/251,318.00AAPL#N/A#N/A
42/13/25$ 241.532/13/25$ 74.452/13/25$ 480.492/14/251,320.00AZN#N/A#N/A
52/12/25$ 236.872/12/25$ 74.432/12/25$ 471.732/13/251,328.00BRK.B#N/A#N/A
62/11/25$ 232.622/11/25$ 72.732/11/25$ 472.612/12/251,332.00BRSC#N/A#N/A
72/10/25$ 227.652/10/25$ 72.662/10/25$ 470.172/11/251,340.00
82/7/25$ 227.632/7/25$ 71.992/7/25$ 472.742/10/251,344.00
92/6/25$ 233.222/6/25$ 72.362/6/25$ 477.222/7/251,346.00
102/5/25$ 232.472/5/25$ 70.942/5/25$ 473.732/6/251,358.00
112/4/25$ 232.802/4/25$ 68.962/4/25$ 467.492/5/251,344.00
122/3/25$ 228.012/3/25$ 69.862/3/25$ 464.612/4/251,340.00
131/31/25$ 236.001/31/25$ 70.761/31/25$ 468.672/3/251,350.00
141/30/25$ 237.591/30/25$ 71.241/30/25$ 472.351/31/251,364.00
151/29/25$ 239.361/29/25$ 70.251/29/25$ 469.261/30/251,356.00
161/28/25$ 238.261/28/25$ 69.591/28/25$ 469.971/29/251,346.00
171/27/25$ 229.861/27/25$ 70.081/27/25$ 474.701/28/251,348.00
181/24/25$ 222.781/24/25$ 69.061/24/25$ 463.191/27/251,330.00
191/23/25$ 223.661/23/25$ 68.601/23/25$ 459.831/24/251,340.00
201/22/25$ 223.831/22/25$ 68.201/22/25$ 460.511/23/251,340.00
211/21/25$ 222.641/21/25$ 67.961/21/25$ 468.571/22/251,344.00
221/17/25$ 229.981/17/25$ 66.601/17/25$ 467.951/21/251,352.00
Sheet1
Cell Formulas
RangeFormula
L3:M6L3=LET(R,MATCH(L$2,INDEX($A$3:$H$22,,MATCH($K3,$A$1:$H$1,0)),0),INDEX($A$3:$H$22,R,MATCH($K3,$A$1:$H$1,0)+1))
 
Upvote 0
I guess change A1 to B1,

=LET(R,MATCH(L$2,INDEX($A$3:$H$22,,MATCH($K3,$A$1:$H$1,0)),0),INDEX($A$3:$H$22,R,MATCH($K3,$A$1:$H$1,0)+1))
 
Upvote 0
Solution
That seems to work - thanks.

Book1
KLM
22/3/252/4/25
3AAPL$ 228.01$ 232.80
4AZN$ 69.86$ 68.96
5BRK.B$ 464.61$ 467.49
6BRSC1,350.001,340.00
Sheet1
Cell Formulas
RangeFormula
L3:M6L3=LET(R,MATCH(L$2,INDEX($A$3:$H$22,,MATCH($K3,$B$1:$H$1,0)),0),INDEX($A$3:$H$22,R,MATCH($K3,$B$1:$H$1,0)+1))


So the correct formula would be as shown in L3: =LET(R,MATCH(L$2,INDEX($A$3:$H$22,,MATCH($K3,$B$1:$H$1,0)),0),INDEX($A$3:$H$22,R,MATCH($K3,$B$1:$H$1,0)+1))

My actual table size in the working version is actually B3:CQ1266 so it's going to be a very costly formula regarding resources used. If anyone knows of a less power-consuming formula, I'd welcome it.
 
Upvote 0
المصنف1
ABCDEFGHIJK
1AAPLAZNBRK.BBRSC
2DateCloseDateCloseDateCloseDateClose2/3/2025BRSC
302/14/2025244.602/14/202573.5802/14/2025479.5902/17/202513181350
402/13/2025241.5302/13/202574.4502/13/2025480.4902/14/20251320
502/12/2025236.8702/12/202574.4302/12/2025471.7302/13/20251328
602/11/2025232.6202/11/202572.7302/11/2025472.6102/12/20251332
702/10/2025227.6502/10/202572.6602/10/2025470.1702/11/20251340
802/07/2025227.6302/07/202571.9902/07/2025472.7402/10/20251344
902/06/2025233.2202/06/202572.3602/06/2025477.2202/07/20251346
1002/05/2025232.4702/05/202570.93502/05/2025473.7302/06/20251358
1102/04/2025232.802/04/202568.9602/04/2025467.4902/05/20251344
1202/03/2025228.0102/03/202569.8602/03/2025464.6102/04/20251340
1301/31/202523601/31/202570.7601/31/2025468.6702/03/20251350
1401/30/2025237.5901/30/202571.2401/30/2025472.3501/31/20251364
1501/29/2025239.3601/29/202570.2501/29/2025469.2601/30/20251356
1601/28/2025238.2601/28/202569.5901/28/2025469.9701/29/20251346
1701/27/2025229.8601/27/202570.0801/27/2025474.701/28/20251348
1801/24/2025222.7801/24/202569.0601/24/2025463.1901/27/20251330
1901/23/2025223.6601/23/202568.601/23/2025459.8301/24/20251340
2001/22/2025223.8301/22/202568.201/22/2025460.5101/23/20251340
2101/21/2025222.6401/21/202567.9601/21/2025468.5701/22/20251344
2201/17/2025229.9801/17/202566.601/17/2025467.9501/21/20251352
Sheet1
Cell Formulas
RangeFormula
J3J3=LET(X,$B$3:$H$22,MAX(X*($J$2=$A$3:$G$22)*($B$1:$H$1=$K$2)*(SEQUENCE(ROWS(X),COLUMNS(X))^0)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:H22Expression=AND(OFFSET(A3,0,-1)=$J$2,A3=$J$3,COLUMN(A3)=MAX(COLUMN($A$1:$H$1)*($A$1:$H$1=$K$2)))textNO
A3:H22Expression=AND(MAX((A3=$J$2)*($B$1:$H$1=$K$2)*(SEQUENCE(1,COLUMNS($B$3:$H$22))^0)*$J$3),COLUMN(A3)=MAX(($B$1:$H$1=$K$2)*(COLUMN($B$1:$H$1)-1)))textNO
Cells with Data Validation
CellAllowCriteria
K2List=$B$1:$H$1
 
Upvote 0
Sam_D_Ben said:
I guess change A1 to B1,

=LET(R,MATCH(L$2,INDEX($A$3:$H$22,,MATCH($K3,$A$1:$H$1,0)),0),INDEX($A$3:$H$22,R,MATCH($K3,$A$1:$H$1,0)+1))
Click to expand...
Hi there, just to clarify — that solution wasn't mine. The work belongs to @Jeffrey Mahoney. I only assisted in identifying the error. Jeffrey might be tied up at the moment and unable to respond, but his solution was spot-on. Kindly mark his response as the accepted solution. Thank you!
 
Upvote 0
Thanks, Sam. I get your point. However, if I mark Jeffery's solution as correct, it would mislead any future readers. Your correction needs to be applied for it to work.

I also want to spend a little time analysing Fahad's solution to see if it is less power-hungry.
 
Upvote 0
To Fahad: Very ingenious! I confess to not completely understanding the logic. However, I can get it to work fine on my test table shown above, but it fails when I adapt it to a table of 92 columns and 1260 rows. I get #VALUE! responses to all variables.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
How to make it Highlight in Any color while selecting the name
Replies
1
Views
84
DanteAmor
DanteAmor
R
  • Solved
Application.Intersect Range targeting Another Sheet's Cell Value
Replies
5
Views
156
Joe4
Joe4
G
  • Solved
Interpolating when some of the input values are errors
Replies
6
Views
138
Glove_Man
G
R
  • Question Question
COUNTIFS using MONTH and YEAR references
2
Replies
13
Views
299
Dave Patton
D
G
  • Question Question
Lookup Table/column Header based on Last Row of Data (first blank value found)
Replies
7
Views
142
Fluff
Fluff

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