# Index - Match Formula with Dates

#### gsmith923

##### Board Regular
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Because you are adding +1 to the row location of the INDEX function.

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:
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)

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.

=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))

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.

Oops... yup. That could be a problem.

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))

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.

What do you want for a pre 01/31 date?

Replies
2
Views
251
Replies
4
Views
281
Replies
8
Views
270
Replies
1
Views
235
Replies
0
Views
117

1,196,113
Messages
6,013,547
Members
441,770
Latest member
Griggsy28

### 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.

### Which adblocker are you using?

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

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