• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF with Index

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
 

Attachments

GraH - Guido

Well-Known Member
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

Well-Known Member
In cell V8:
Code:
=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
 

Attachments

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
 

Attachments

Last edited by a moderator:

p45cal

Well-Known Member
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.
 
Top