Alternative for if condition

[ganeshm]

Hi...

I have posted an excel file containing if condition. I would like to reduce the formula as it is so lengthier in "B9". Is there any alternative formula for the same. Need ur advice.

Regards,
ganeshm

 

[Deepak]

Check this.

=LOOKUP(A9,A2:A6,D2:D6)

or

=LOOKUP(A9,A2:A6,ROW(A2:A6)-1)

 

[Deepak]

Some more!


=SUMPRODUCT((A2:A6<=A9)*(C2:C6>=A9))

CSE
=MATCH(1,(A2:A6<=A9)*(C2:C6>=A9),0)

 

[ganeshm]

great dude.

Thanks for minimizing if condition. It helps me a lot.

 

[NARAYANK991]

Hi Ganesh ,

In fact your IF formula is slightly wrong , which is because enough testing has not been done.

When ever you write a formula , test it with as much data as possible ; there are three kinds of data you should use for testing purposes :

1. Valid data or normal data , which will occur in the course of usage

2. Abnormal data , which will never happen in the course of usage , but may happen because of user error ; such abnormal data should be caught , and should not result in a normal result

3. Boundary data , which is the lower and upper limits of individual ranges and well as the overall range.

When you test your data in this fashion , you will see that the original formula had the following highlighted error :

=IF(AND(A9>=A2,A9<=C2),1,IF(AND(A9>=A3,A9<=C3),2,IF(AND(A9>=A4,A9<=C4,),3,IF(AND(A9>=A5,A9<=C5),4,5))))

Test your original formula with a value such as 333.

Narayan

 

[Hui]

=VLOOKUP(A9,A2:D6,4,TRUE)

 

[NARAYANK991]

Hi Ganesh ,

Going by the data you have given , and the expected results , even the following formula should work :

=MATCH(A9,A2:A6)

Narayan