Nested IF with Index

[msharma864512]

Hi I am trying to combine Nested IF with Index Match formula.

I have ranks of a list of companies and on the basis of their ranks, their categories have been defined (First 25% as Out performers, 25% to 75% as Avg Performers, and so on)

Details are in the attachment

I have already created a formula for whole data set, but now i want the category to be defined by sectors

Minimum and Maximum Rank is different in all the sectors, details are in excel

For e.g if we are designating the Rank to Technology company, then it should use the cell "AB3" to compare with minimum and maximum,

if we are categorizing Oil & Gas companies, then it should use the cell "AB7" to compare.

Please help me with this, formula has to be written in column V

 

[GraH - Guido]

So you deleted the content of column N, though it is used in the formula in column U which is used in the formula you are after. That leaves us no way to evaluate the result.
In your formula replace $AB$3 with index($AB$3:$AB$16;match(T8;$AC$3:$AC$16;0).

I'm sure there is a better way though.

 

[p45cal]

In cell V8:

=INDEX($Y$1:$AA$1,MATCH($U8,INDEX($X$3:$AA$16,MATCH($T8,$AC$3:$AC$16,0),0)))

copied down.
For this to work you must have:

1. values in column N
2. zeroes in cells X3:X16

 

[msharma864512]

Thanks for the help,

I have added my values in Column N, and also dragged the whole formula, but in many cases it is showing REF Error

 

[p45cal]

but in many cases it is showing REF Error

Well, 14 cases out of 2k, the number of Sectors.

In cell AA3:
change from:
=ROUND($AB3*AA$2,0)
to:
=$AB3*AA$2+0.0001
(and format the cell to Number with 0 decimal places to keep it looking right)

Copy that down to cell AA16.
That's it.