Using a rounded number in a formula

[ddocg]

Hi,

i was kindly provided a formula as a solution to a problem though, it didn't work 100%.

I believe the issue is due to the formula using a rounded number and would like to know of a solution if possible?

The formula is

=INDEX(J1:AC1,MATCH(D8,INDEX(J2:AC7,MATCH(F4,I2:I7,0),0),-1))

F4 in the above formula is where the rounded number occurs as F4 is the result of D4 divided by E4

i2:i7 contains 0.2, 0.3, 0.4, 0.5, 0.6, 0.7

I note that if the answer in F4 is not exactly 0.2, 0.3, 0.4, 0.5, 0.6 or 0.7 then the formula returns #N\A

Is there a way to make the formula take the rounded number in F4 and not the unrounded number as it appears to be doing?

Dave

 

[ajamess]

you could just replace F4 with

round(f4,1)

 

[ddocg]

Many thanks though, I assume you mean replace F4 in the formula with round(F4,1) so it would read

=INDEX(J1:AC1,MATCH(D8,INDEX(J2:AC7,MATCH(ROUND(F4,1),I2:I7,0),0),-1))

I have tried it and it works again many thanks for your help

My only issue now concerns the -1 at the end of the formula..... it is not returning the next lowest value in J1:AC1 rather it returns the next highest.... if I change the -1 to just 1 then it returns the absolute highest value in J1:AC1

hmmmmmmmm