Index - Match Formula with Dates

gsmith923

Board Regular
Joined
Jul 31, 2007
Messages
50
I am currently using the following formula and it gives the result P-10, P-10, P-11 below dates that are manually entered. I can not figure out why the date 10/31/09 returns a P-11 value when it matches a P-10 value? All other dates work expcept for the 12 period ending dates. Any help would be greatly appreciated.

=INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,1)+1,0)

10/17/09 10/24/09 10/31/09
P-10 P-10 P-11

01/31/09 P-01
02/28/09 P-02
04/04/09 P-03
05/02/09 P-04
05/30/09 P-05
07/04/09 P-06
08/01/09 P-07
08/29/09 P-08
10/03/09 P-09
10/31/09 P-10
11/28/09 P-11
01/02/10 P-12
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Because you are adding +1 to the row location of the INDEX function.
 
Upvote 0
If you want to find the value for the next available date, maybe

=INDEX($B$5:$B$16,MATCH(A$1-1,$A$5:$A$16,1),0)

edit: didn't mean to take out the +1

=INDEX($B$5:$B$16,MATCH(A$1-1,$A$5:$A$16,1)+1,0)
 
Last edited:
Upvote 0
Why not just change the match type from 1 to -1 and get rid of the +1 to the row index.

I didn't test this, but try this instead:

=INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,-1),0)
 
Upvote 0
Why not just change the match type from 1 to -1 and get rid of the +1 to the row index.

I didn't test this, but try this instead:

=INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,-1),0)

I'm sure that won't work, since using -1 as the match_type argument in the MATCH function requires the data to be in descending order.
 
Upvote 0
How about this...

=IF(ISNA(VLOOKUP(A$1,$A$5:$B$16,2,0)),INDEX($B$5:$B$16,MATCH(A$1,$B$5:$B$16,1)+1,0),VLOOKUP(A$1,$A$5:$B$16,2,0))
 
Upvote 0
Sorry I got some of the ranges messed up

=IF(ISNA(VLOOKUP(A$1,$A$5:$B$16,2,0)),INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,1)+1,0),VLOOKUP(A$1,$A$5:$B$16,2,0))
 
Upvote 0
Thanks JRB! This worked on all dates entered except any date between 01/01/09 and 01/30/09. I tested all other days and they returned the correct value, but for the pre 01/31 dates a N/A value was returned.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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