Match using -1 not working when looking for a date

sammysrefuge

New Member
Joined
Jun 9, 2015
Messages
6
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
14/15/2015
2
34/14/2015
44/15/2015
54/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
14/15/2015
2
34/14/2015
44/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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Mackers

Well-known Member
Joined
Nov 4, 2013
Messages
536
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
 

sammysrefuge

New Member
Joined
Jun 9, 2015
Messages
6
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
 

Mackers

Well-known Member
Joined
Nov 4, 2013
Messages
536
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
 

sammysrefuge

New Member
Joined
Jun 9, 2015
Messages
6

ADVERTISEMENT

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
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Try:
Excel Workbook
ABC
14/15/2015
2
34/14/2015
44/16/2015
54/18/2015
64
7
Sheet
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
See Jonmo1 post above. He is right in that the formula I gave will fail on an exact match. Use his formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

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
Top