# Match using -1 not working when looking for a date

#### sammysrefuge

##### New Member
I am trying to find the row number for a specific value where the value in the list equals or is greater than the value I am searching for.

When looking for the exact value in a list, the following works:

 A 1 4/15/2015 2 3 4/14/2015 4 4/15/2015 5 4/16/2015

<tbody>
</tbody>

=MATCH(A1,A3:A5,0)+ROW(A3:A5)-1

The formula above returns 4 which is correct.

However, when looking for a value that is "greater than" then it doesn't work. See below:

 A 1 4/15/2015 2 3 4/14/2015 4 4/16/2015

<tbody>
</tbody>

=MATCH(A1,A3:A5,-1)+ROW(A3:A5)-1

I want the formula above to return a 4 but instead it returns "#N/A"

Any suggestions?

Thanks,

Jason

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi Jason

For MATCH() to work with a -1 match_type, the lookup_array has to be sorted in descending order.

Hope that helps

Mackers

Hi Jason

For MATCH() to work with a -1 match_type, the lookup_array has to be sorted in descending order.

Hope that helps

Mackers

That makes sense. Is there a way to do this when the values are in ascending order? The data file that I am referencing is always going to be in ascending order.

Thanks,

Jason

Try the following as an array function (I'm sure there is a more efficient way that one of the MVPs can show you but this should work):

=MATCH(MIN(IF(A3:A5>A1, A3:A5)), IF(A3:A5>1, A3:A5), 0)+ROW(A3)-1

This essentially creates an array of dates that are greater than your lookup date, then finds the minimum of those dates, then finds the position of said minimum date-greater-than-your-date.

Hope that helps

Mackers

Try the following as an array function (I'm sure there is a more efficient way that one of the MVPs can show you but this should work):

=MATCH(MIN(IF(A3:A5>A1, A3:A5)), IF(A3:A5>1, A3:A5), 0)+ROW(A3)-1

This essentially creates an array of dates that are greater than your lookup date, then finds the minimum of those dates, then finds the position of said minimum date-greater-than-your-date.

Hope that helps

Mackers

This works great. However, I need to use this inside of a non-array formula. Is there a way to do this without using an array formula or is there a way to use an array formula inside of a non-array formula? I know I can put the array in a separate column and then reference this value but I would rather not have to add another column.

Thanks,

Jason

Try:
Excel Workbook
ABC
14/15/2015
2
34/14/2015
44/16/2015
54/18/2015
64
7
Sheet

That's on the right track AhoyNC
But it will be wrong when there actually is an exact match.

Assuming XL2007+, try
=IFERROR(MATCH(A1,A3:A5,0)+ROW(A3:A5)-1,MATCH(A1,A3:A5,1)+ROW(A3:A5))

Try:
 A B C 1 4/15/2015 2 3 4/14/2015 4 4/16/2015 5 4/18/2015 6 4 7

<tbody>
</tbody>

 Cell Formula B6 =MATCH(A1,A3:A5,1)+ROW(A3:A5)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This worked perfect!

Thanks so much,

Jason

See Jonmo1 post above. He is right in that the formula I gave will fail on an exact match. Use his formula.

Replies
6
Views
407
Replies
1
Views
271
Replies
8
Views
540
Replies
2
Views
312
Replies
1
Views
230

1,206,762
Messages
6,074,788
Members
446,089
Latest member
Andrew123456789

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