Query related to Nested IF Formula in MS Excel 2002

[ravigupta73]

I have an excel sheet where I am getting summery of information by selecting the product in a dropdown menu.

I have used nested IF formula to achieve this.

This formula is working fine but taking only 7 arguements.

I want to add more arguements. The formula is given below: Please help!

=IF($B$2="Sub Base",VLOOKUP(A4,$IG$94:$IS$120,2,0),IF($B$2="Revenue",VLOOKUP(A4,$IG$3:$IS$29,2,0),IF($B$2="Gross Adds",VLOOKUP(A4,$IG$34:$IS$60,2,0),IF($B$2="Churn",VLOOKUP(A4,$IG$64:$IS$90,2,0),IF($B$2="Net Adds",VLOOKUP(A4,$IG$124:$IS$150,2,0),IF($B$2="ARPU",VLOOKUP(A4,$IG$154:$IS$180,2,0)))))))

 

[shajueasow]

The nesitng level limit IS upto seven levels. Otherwise you will have to use excel 2007.

 

[sailepaty]

This could be a solution:

=VLOOKUP(A4,CHOOSE(MATCH($B$2,{"Sub Base";"Revenue";"Gross Adds";"Chum";"Net Adds";"ARPU";"NEW OPTION"},0),$IG$94:$IS$120,$IG$3:$IS$29,$IG$34:$IS$60,$IG$64:$IS$90,$IG$124:$IS$150,$IG$154:$IS$180,NEW RANGE),2,0)

Change the NEW OPTION and NEW RANGE for your real Text and Range.

Regards