MATCH function - match_type question

[MJBVLC]

Hi,

MATCH(lookup_value,lookup_array,match_type)

I am using the MATCH function to specify in which row of a large table I want to be looking up values.

The column I am MATCH-ing contains numbers in ascending order. For smaller values, the increment is 0.1 , then 0.5, finally the numbers are all consecutive integers.

If my input value is exactly matched in the table, I want the match to be that value. If there is no exact match, I want to go to the next greatest. In the words of Microsoft Help, I want to "find the smallest value that is greater than or equal to lookup_value". However, this is consistent with match_type -1 which only works for data in descending order.

I have got this to work with an IF statement that first checks if my input value is in the lookup column and then adds 1 to the MATCH value if not:
=IF(ISNA(VLOOKUP(input,lookup,1,FALSE)),MATCH(input,lookup)+1,MATCH(input,lookup))

But this is very inelegant and will make my final formulae where is value is to be used very long.

I'm surprised not to find a million of these questions posted on the board - perhaps there's a neat solution that everyone except me knows about?

Thanks,
Melanie

 

[Aladin Akyurek]

Can we have the range "lookup" refers to?

 

[MJBVLC]

It is a 780 row column running from 1 to 690. Up to value 10 it increases in 0.1 increments, then up to value 20 it increases in 0.5 increments. So a samples from the range are:
1
1.1
1.2
1.3
:
:
9.7
9.8
9.9
10
10.5
11
11.5
:
:
:
19.5
20
21
22

I need the following input - row combinations:

9.25 -> 9.3
10.25 -> 10.5
20.25 -> 21

Thanks,
Melanie

 

[Aladin Akyurek]

Does "lookup" stand for A2:B700 or what?

 

[MJBVLC]

The actual range in which this data is stored is M6:M768. I'm just using the LOOKUP function to see if there is an exact match for my input. If not, I add 1 to the value of the MATCH function if I need to move into the next row down.

I want to avoid using LOOKUP instead of MATCH because I have data to the left of the M column which I need to return using MATCH / INDEX functions.

Thanks,
M

 

[Aladin Akyurek]

The actual range in which this data is stored is M6:M768. I'm just using the LOOKUP function to see if there is an exact match for my input. If not, I add 1 to the value of the MATCH function if I need to move into the next row down.

I want to avoid using LOOKUP instead of MATCH because I have data to the left of the M column which I need to return using MATCH / INDEX functions.

Thanks,
M

Lets say that you want results from K6:K768.

Try...

=INDEX($K$6:$K$768,MATCH(Q6,$M$6:$M$768,1)+(LOOKUP(Q6,$M$6:$M$768) < Q6))

where Q6 houses a lookup value of interest.

Adjust the ranges to suit...

 

[MJBVLC]

Wonderful!

It's still not as elegant as I had hoped - but the concept of adding the value of TRUE / FALSE is a new one on me and something that will be very useful to me in other areas.

I feel like a new door has opened.......

Thank you very much.....now got about a million formulae to edit!

Melanie

 

[Aladin Akyurek]

Wonderful!

It's still not as elegant as I had hoped - but the concept of adding the value of TRUE / FALSE is a new one on me and something that will be very useful to me in other areas.

I feel like a new door has opened.......

Thank you very much.....now got about a million formulae to edit!

Melanie

Melanie,

It's fast for sure (and elegant to me)...

 

[MJBVLC]

You're right. It is elegant and I have already raved about it to a colleague.

Maybe I should have said "It's still not as short as I had hoped". It is used several times within each formula so they're becoming a bit hard to decipher. My gripe is with Excel for only rounding in one direction.