Nested if statement

**pamc** · 03-08-2013, 10:59 AM

I need to assign tiers based on a range of scores. Not sure how to set this up in a nested if statement. Sample spreadsheet is attached.

Score Tier
>=100 T1
80-99 T2
60-79 T3
40-59 T4
0-39 T5

**benishiryo** · 03-08-2013, 11:07 AM

hi pamc, welcome to the forum. change the values in A2:A6 to show just the Max score needed to obtain the tier. so:
A2: ?
A3: 99
A4: 79
A5: 59
A6: 39

then in E2:
=INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,-1))
if there's no maximum value for A2, then use this for A2:
=MAX(D2:D6)

and if you do not want to replace your values in A2:A6, then have this table separately

**Kevin UK** · 03-08-2013, 11:10 AM

Hi

Try the VLOOKUP see the attached file.

**pamc** · 03-08-2013, 11:18 AM

Thank you both! Works either way.

Kevin, what does the 1 do in the range lookup? I've only used true or false before.

**Kevin UK** · 03-08-2013, 11:22 AM

Hi pamc

1 = TRUE
0 = FALSE

**pamc** · 03-08-2013, 11:23 AM

Hi. That's kinda what I thought, just wanted to make sure.

Thanks again for your help!

**Kevin UK** · 03-08-2013, 11:26 AM

pamc

No problem, thanks for the feed back.

**syedfahadjalali** · 03-08-2013, 11:43 AM

I have a question in this case. what we need to do if i want to place the result in an order with their values

I got all T1,T2----T6 alongwith the d column that contains values. i want to do them in order
all t1 values positioned at top then t2 values and so on. so that i can group them . please obliged me in this scenario