Index formula

[Afsha]

I am trying to use the index formula but need to incorporate an IF condition.
For some reason, the formula does not seem to be picking up the second IF
condition.

I am trying to say if the number is cell X is more than 7 then use the first
matrix, if the number is less than 8 then use the second matrix.... below is
a copy of the formula, can someone tel me where I am going wrong?

=IF(E31>7,(INDEX($BD$21:$BJ$26,MATCH($N31,$BD$21:$BD$26),MATCH($AU31,$BD$21:$BJ$21),IF(E31<8,(INDEX($BK$21:$BQ$26,MATCH($N31,$BK$21:$BK$26),MATCH($AU31,$BK$21:$BQ$21)))))))

thanks

 

[Jim Thomlinson]

Your logic does not make sense. 7, 7.5, 8, 9, 10, 100, ... are all greater
than 7 so the first matrix will be used. You will never get to the second
matrix... So what happens if you flip it around an go with less than 8 as the
first criteria. Now 7.5 is less than 8 so is uses the second matrix. But by
your description it should be using the first matrix. All values between 7
and 8 use both matricies. With your existing formula a value less than or
equal to 7 should use the second matrix.

 

[Afsha]

Hi Jim,

I have tried to flip the criteria:

=IF(E31<8,(INDEX($BD$21:$BJ$26,MATCH($N31,$BD$21:$BD$26),MATCH($AU31,$BD$21:$BJ$21),IF(E31>7,(INDEX($BK$21:$BQ$26,MATCH($N31,$BK$21:$BK$26),MATCH($AU31,$BK$21:$BQ$21)))))))

I then put 8 in my criteria and the result I get is FALSE, and when I put 6,
I get #VALUE?

your help is much appreciated.

 

[Jim Thomlinson]

Get your 2 index / match formulas working seperately as stand alone formulas
to ensure that there is no issues there. Secondly you need to determine what
your criteria are supposed to be. Forwards or backwards it does not make
sense. What happens to values between 7 and 8 inclusive?