How to lookup a value from several rows in a table using INDEX and MATCH

[plan]

Hi everyone,
First post, so be gentle! I have spent hours trying to work out a solution to this issue, and know someone here will know the answer.

We have data in a large table with a column for each month and then new rows for each year (see screenshot). I need to lookup the value corresponding to a future date. The obvious way to do this is with an Array Formula using Index and Match. I'm reluctant to use Helper Cells, as there are 650 rows in the table, so this will make the table very large and confusing (the screenshot is a small subset of the data). I'm also reluctant to move everything to one row for the same reason. We use Excel 2019 so we can't use a Dynamic Array. Please can someone point me in the right direction?

Jason

 

[kweaver]

Welcome to Mr. Excel...

If you would please use XL2BB to post your sample data, anyone trying to solve your problem won't have to recreate your data.

What is the answer you want where the #value! appears? Is it 107,452.64?

 

[kweaver]

How are there 600+ rows? Is there another block of 5 rows for Feb 2024 through Jan 2025, etc., etc.? Then, where is your search being placed in the data -- below 600+ rows?

 

[plan]

Hi Kweaver,
Thank you for your prompt response. Yes, I'm looking to return the value after charges for the date in question - £107,452.64 in this example.
The reason for 600+ rows is that we are calculating the charges up to 40 years into the future, and each year has a dozen different rows to show values before and after different fees.
I've pasted a subset of the data below. Thanks so much for your help!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22
2	Investment Charges	£11.64	£11.68	£11.73	£11.77	£11.82	£11.86	£11.91	£11.95	£12.00	£12.04	£12.09	£12.13
3	Adviser Charges	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
4	Platform Service Fee	£16.73	£16.80	£16.86	£16.92	£16.99	£17.05	£17.12	£17.18	£17.25	£17.31	£17.38	£17.44
5	Value After Charges	£100,379.04	£100,759.51	£101,141.42	£101,524.78	£101,909.59	£102,295.86	£102,683.59	£103,072.79	£103,463.47	£103,855.63	£104,249.28	£104,644.42
6	Month	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23
7	Investment Charges	£12.18	£12.23	£12.27	£12.32	£12.37	£12.41	£12.46	£12.51	£12.55	£12.60	£12.65	£12.70
8	Adviser Charges	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
9	Platform Service Fee	£17.51	£17.58	£17.64	£17.71	£17.78	£17.84	£17.91	£17.98	£18.05	£18.12	£18.18	£18.25
10	Value After Charges	£105,041.06	£105,439.20	£105,838.85	£106,240.01	£106,642.69	£107,046.90	£107,452.64	£107,859.92	£108,268.75	£108,679.12	£109,091.05	£109,504.54
11
12	Future value date:	31/08/22
13	Value after charges on that date:
Sheet1

 

[kweaver]

I tried a macro with dates messed with because I have a US date on my system. And, I moved the search info away from the potential 600+ lines.
See what you think:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Month	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22		Future Value Data:	May-21
2	Investment Charges	£11.64	£11.68	£11.73	£11.77	£11.82	£11.86	£11.91	£11.95	£12.00	£12.04	£12.09	£12.13		Value after Chgs	£101,524.78
3	Adviser Charges	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
4	Platform Service Fee	£16.73	£16.80	£16.86	£16.92	£16.99	£17.05	£17.12	£17.18	£17.25	£17.31	£17.38	£17.44
5	Value After Charges	£100,379.04	£100,759.51	£101,141.42	£101,524.78	£101,909.59	£102,295.86	£102,683.59	£103,072.79	£103,463.47	£103,855.63	£104,249.28	£104,644.42
6	Month	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23
7	Investment Charges	£12.18	£12.23	£12.27	£12.32	£12.37	£12.41	£12.46	£12.51	£12.55	£12.60	£12.65	£12.70
8	Adviser Charges	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
9	Platform Service Fee	£17.51	£17.58	£17.64	£17.71	£17.78	£17.84	£17.91	£17.98	£18.05	£18.12	£18.18	£18.25
10	Value After Charges	£105,041.06	£105,439.20	£105,838.85	£106,240.01	£106,642.69	£107,046.90	£107,452.64	£107,859.92	£108,268.75	£108,679.12	£109,091.05	£109,504.54
11	Month	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24
12	Investment Charges	11.39	16.1	13.77	16.94	18.45	11.18	16.53	18.65	16.78	18.83	10.26	13.39
13	Adviser Charges
14	Platform Service Fee
15	Value After Charges	£117,717.93	£136,807.31	£130,669.80	£117,880.36	£136,455.97	£114,753.42	£105,385.99	£130,446.97	£133,322.74	£133,493.10	£149,550.71	£114,084.63
Sheet2

Cell Formulas
Range	Formula
C11:M11,C6:M6,C1:M1	C1	=EDATE(B1,1)
B6,B11	B6	=EDATE(M1,1)

Sub MatchYrMo()
    Dim i As Long, lr As Long, col As Variant
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lr Step 5
        col = Application.Match(Cells(1, 16), Range("A" & i & ":M" & i), 0)
        If Not IsError(col) Then
          Cells(2, 16) = Cells(i + 4, col).Value
          End
        Else
        End If
    Next i
End Sub

 

[plan]

That's brilliant - I'll give the macro a try. I also came up with another alternative, which is to add a column with a simple horizontal lookup for each date row, and return a nil value if the dates don't match the date in B12. That way the total of that column will match the value I'm looking for. What do you think?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22
2	Investment Charges	£11.64	£11.68	£11.73	£11.77	£11.82	£11.86	£11.91	£11.95	£12.00	£12.04	£12.09	£12.13
3	Adviser Charges	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
4	Platform Service Fee	£16.73	£16.80	£16.86	£16.92	£16.99	£17.05	£17.12	£17.18	£17.25	£17.31	£17.38	£17.44
5	Value After Charges	£100,379.04	£100,759.51	£101,141.42	£101,524.78	£101,909.59	£102,295.86	£102,683.59	£103,072.79	£103,463.47	£103,855.63	£104,249.28	£104,644.42	0.00
6	Month	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23
7	Investment Charges	£12.18	£12.23	£12.27	£12.32	£12.37	£12.41	£12.46	£12.51	£12.55	£12.60	£12.65	£12.70
8	Adviser Charges	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
9	Platform Service Fee	£17.51	£17.58	£17.64	£17.71	£17.78	£17.84	£17.91	£17.98	£18.05	£18.12	£18.18	£18.25
10	Value After Charges	£105,041.06	£105,439.20	£105,838.85	£106,240.01	£106,642.69	£107,046.90	£107,452.64	£107,859.92	£108,268.75	£108,679.12	£109,091.05	£109,504.54	17.91
11
12	Future value date:	31/08/22
13	Value after charges on that date:	£17.91
Sheet1

Cell Formulas
Range	Formula
N5,N10	N5	=IFNA(HLOOKUP($B$12,B1:M5,4,0),0)
B13	B13	=SUM(N2:N10)

 

[kweaver]

You didn't want a helper column initially, but I think that's a good idea.
You can get the values for each row of totals through a macro so you don't have to copy and paste.

I took this approach:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22
2	Investment Charges	11.64	11.68	11.73	11.77	11.82	11.86	11.91	11.95	12	12.04	12.09	12.13
3	Adviser Charges	0	0	0	0	0	0	0	0	0	0	0	0
4	Platform Service Fee	16.73	16.8	16.86	16.92	16.99	17.05	17.12	17.18	17.25	17.31	17.38	17.44
5	Value After Charges	100,379.04	100,759.51	101,141.42	101,524.78	101,909.59	102,295.86	102,683.59	103,072.79	103,463.47	103,855.63	104,249.28	104,644.42	0
6	Month	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23
7	Investment Charges	12.18	12.23	12.27	12.32	12.37	12.41	12.46	12.51	12.55	12.6	12.65	12.7
8	Adviser Charges	0	0	0	0	0	0	0	0	0	0	0	0
9	Platform Service Fee	17.51	17.58	17.64	17.71	17.78	17.84	17.91	17.98	18.05	18.12	18.18	18.25
10	Value After Charges	105,041.06	105,439.20	105,838.85	106,240.01	106,642.69	107,046.90	107,452.64	107,859.92	108,268.75	108,679.12	109,091.05	109,504.54	0
11	Month	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24
12	Investment Charges	11.39	16.1	13.77	16.94	18.45	11.18	16.53	18.65	16.78	18.83	10.26	13.39
13	Adviser Charges
14	Platform Service Fee
15	Value After Charges	£117,717.93	£136,807.31	£130,669.80	£117,880.36	£136,455.97	£114,753.42	£105,385.99	£130,446.97	£133,322.74	£133,493.10	£149,550.71	£114,084.63	133493.1
16
17
18
19
20		Nov-23
21		£133,493.10
Sheet3

Cell Formulas
Range	Formula
N5,N15,N10	N5	=IFERROR(HLOOKUP($B$20,B1:M5,5,0),0)
B11	B11	=EDATE(M6,1)
C11:M11	C11	=EDATE(B11,1)
B21	B21	=SUM(N:N)

 

[kweaver]

I then did this macro:

Sub SumOnDate()
Dim i As Long, lr As Long, ival As Double
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lr Step 5
    ival = WorksheetFunction.IfError(Application.HLookup(Range("B20"), Range("B" & i & ":M" & i + 4), 5, 0), 0)
    Cells(i + 4, 14) = ival
    Next i
    Cells(21, 2) = WorksheetFunction.Sum(Range("N:N"))
       
End Sub

 

[plan]

That's absolutely correct! I was thinking I would need to repeat all the info in the helper column. I prefer your suggestion to us IFERROR and have already used EDATE in the actual spreadsheet, so you've solved my problem!

Thank you so much!