Need greater than = .. less than = combined with index/match state

[Dingy101]

Excel 2003

I am using formula below succesfully , but I need to expand on it

=INDEX(K3:AM30,MATCH(A4,K3:K30,0),MATCH(A5,K3:AM3,0))

What I have is in K3:K30 is a range of numbers 0,6,11,16,21,26,...

I also have in L3:L30 is a range of numbers 5,10,15,20,25,30 ...

0 5
6 10
11 15
16 20
21 25
26 30

Then there is a range of static data filling in the rest of the array

I need to modify the MATCH(A4,K3:K30,0) portion to look at the L column and
K column and pick row that the value in A4 equal to or between.

The above formula will work if I put into cell A4 the exact number that is
in the range K3:K30, and ignore the value in L3:L30.

I need the formula to work if the number 7 is entered in A4 the same as if 6
were entered into A4

In other words I need the match to work on >= K3 and <= L3

Thanks,

Gary

 

[T. Valko]

Try this...

=INDEX(K3:AM30,MATCH(A4,K3:K30),MATCH(A5,K3:AM3,0))

 

[Dingy101]

Biff,

It works thank you!

I see what you did.

Can you explain to me what is happening?

What does the ,0 do?

Gary

 

[T. Valko]

When you have the match_type argument set to 0 that means you're looking for
an exact match. If an exact match isn't found then you get a #N/A error.

Since you're using lookup_values that can be within a range, >=0 <=5, then
you want to set the match_type argument to 1 (or, you can omit the argument
and it will default to 1). This tells the MATCH function that if an exact
match isn't found then find the closest match that is less than the
lookup_value. For example:

MATCH(17,A1:A5)

A1 = 0
A2 = 10
A3 = 20
A4 = 30
A5 = 40

Since there isn't an exact match of 17 it will match the closest number that
is less than 17 and that number is 10. When using a match_type of 1 the
lookup_array *MUST* be sorted in ascending order to get the correct result.

 

[Dingy101]

Thank You,

That was a helpful answer.

It works great

Gary

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Dingy101]

David,

Thank you very that very useless answer.

That type of input is why forums dry up and wither.

See elsewhere in thread for an answer that was actually very useful and
appreciated.

Gary