 # Index Match using < operator

• Hi,

I am using an Index Match formula to determine a charge from a table.

The criteria are Currency and Number of days. I dont fully understand the function and so am having trouble in correcting it as it is producing unexpected results.

The formula is INDEX('Charges Table'!\$C\$2:\$R\$13,MATCH(P79+1,'Charges Table'!\$B\$2:\$B\$13,1),MATCH(G79,'Charges Table'!\$C\$1:\$R\$1,0))

I have a number of days of 48 and this should return a charge of 3.5, being in the range more than 8 and less than 183 but it is returning 3.06

The attached table explains it better than i ever could in words!

What i need is a formula that can determine if the number of days is less than a number but greater than another.

7 DAYS 8< 3.06

8 DAYS TO
6 Months 183< 3.5

Perhaps someone could help me out by taking a look and give me an idea of what i need to do to correct the formula.

Neil

## Files

• Re: Index Match using &lt; operator

Maybe

=INDEX('Charges Table'!\$C\$2:\$R\$13,SUM((MATCH(I4,'Charges Table'!\$B\$2:\$B\$13,1)),1),MATCH(C4,'Charges Table'!\$C\$1:\$R\$1,0))

Tell me if that's messed up.

Bubbis

:silverha:

• Re: Index Match using &lt; operator

Actually, that was just a correction to your formula. I'll work on getting it to where you want --unless someone else beats me to it.

:silverha:

• Re: Index Match using &lt; operator

Maybe this works for you?

Code
``=INDEX('Charges Table'!\$C\$2:\$R\$13,IF(I4<2,0,MATCH(I4,'Charges Table'!\$B\$2:\$B\$13,1))+1,MATCH(C4,'Charges Table'!\$C\$1:\$R\$1,0))``

put in cell J4

• Re: Index Match using &lt; operator

Heck, I'd just subtract 1 from every value in column B, get rid of the 'less than' signs, and then use the formula

=INDEX('Charges Table'!\$C\$2:\$R\$13,SUM((MATCH(I4,'Charges Table'!\$B\$2:\$B\$13,1)),1),MATCH(C4,'Charges Table'!\$C\$1:\$R\$1,0))

in column J. Hope that helps.

Bubbis Thedog

:silverha:

• Re: Index Match using &lt; operator

Thanks very much Bubbis that works. I will have to wait till im back at work in the morning before testing it on the full data.

I think i need to learn the logic behind it so i can fix myself in future.

Thanks to both Bubbis and Seti for the responses.

Cheers
Neil

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!