Solution : Nested IF or VLOOKUP

Level RatingA RatingB RatingC Low Medium High
2 x x x x x x
3 x x x x x x
4 x x x x x x
5 x x x x x x

I need to be able to do nested loops. I want to have a criteria that if a person is in LEVEL 2 and RATINGC and HIGH, then take the value under that.

Please help.

Originally Posted by sachinattri

Level RatingA RatingB RatingC Low Medium High
2 x x x x x x
3 x x x x x x
4 x x x x x x
5 x x x x x x

I need to be able to do nested loops. I want to have a criteria that if a person is in LEVEL 2 and RATINGC and HIGH, then take the value under that.

Please help.

Hi Sachin!

Can you please attach any example? There is something missing in your explaination. Attach a sample file so that we can give you a solution to your problem. Like, what kind of inputs you are expecting in the file etc...what would be the values entered in the columns..either true false of what? Please explain a little more clear

Thanks. This is what I need in an excel format. Basically need to satisfy 3 conditions from 3 different matrix tables and calculate the final value.

Attaching picture of the file. Need to calculate the cell in green if it fits all the criteria before and calculate the NEW VALUE.

Hi Jean-Guy

Thank you very much for taking out the time to solve this. May be I wasnt clear in depicting the problem, but the Rating is in %.

Basically, based on 3 tables/matrix, the final increase has to be calculated on the 100,000. So if John Smith's is a Rating 3 and Level 2 = 11%, and his Skill is Medium = 4% and in his Technical Skill is Semi = 6%. So 11+4+6=21%, so his final value would be 100,000 + 21% = 121000.

Hope this helps.

Hi,

Ok try this:

Please Login or Register  to view this content.

so with a rating of 3 and a level of 2:
INDEX($B$2:$D$6,MATCH(C16,$A$2:$A$6,0),MATCH(D16,$B$1:$D$1,0)) = 11
Skill of 3:
VLOOKUP(E16,$A$9:$B$11,2,0) = 4%
Technical of semi:
VLOOKUP(F16,$D$9:$E$11,2,0) = 6%

basics
100000+((100000*11/100))+(100000*4%)+(100000*6%))
note: if your rating table is formatted the same as the skill and technical tables then you won't need to divide by 100.

Hope that clear it up for you!
Jean-Guy

Thanks very much Pinmaster. If we have more conditions I guess we could just keep adding these.

Thanks VERY Much

A very quick one, we are calculating 3 different % increases and then applying on the main value (100,000 in this case). What if I want to add First Increase to 100,000 and then calculated the second % increase on the Total 100,000 + First % Increase.

Hope I was able to make you understand.