Match using -1 not working when looking for a date

[sammysrefuge]

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:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4/15/2015[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4/14/2015[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4/15/2015[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4/16/2015[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4/15/2015[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4/14/2015[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4/16/2015[/TD]
[/TR]
</tbody>[/TABLE]

=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

 

[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

 

[sammysrefuge]

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]

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]

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]

Try:

Excel Workbook
	A	B	C
1	4/15/2015
2
3	4/14/2015
4	4/16/2015
5	4/18/2015
6		4
7
Sheet

 

[Jonmo1]

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

 

[sammysrefuge]

Try:

	A	B	C

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]4/15/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]4/14/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]4/16/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]4/18/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #ffff00, align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

</tbody>

Spreadsheet Formulas

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

 

[AhoyNC]

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