You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
Thank You,

That was a helpful answer.

It works great

Gary

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dingy101" wrote in message
...
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" wrote:

Try this...

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

--
Biff
Microsoft Excel MVP






"Dingy101" wrote in message
...
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


.



.