how to simplify nested if

[go14344]

this is my formula for IF..

=IF([@4]="r611","r124",IF([@4]="r612","r14",IF([@4]="r711","r125",IF([@4]="r712","r15",IF([@4]="r73011","r12530",IF([@4]="r73012","r1530",IF([@4]="h611","h124",IF([@4]="h612","h14",IF([@4]="h711","h125",IF([@4]="h712","h15",IF([@4]="h73011","h12530",IF([@4]="h73012","h1530",IF([@4]="h7","h3",IF([@4]="r812","r15",IF([@4]="r9301","r2630","")))))))))))))))

is there a way i can simplify it? because plan to add another 6 IF, and it will be too long.. thanks

 

[sandy666]

look at CHOOSE() function

 

[jimrward]

I would probably use VLOOKUP create a two column list of oldvalue newvalue
R611 R124
R612 R14
=VLOOKUP([@4],rangeofyourlist)

 

[Peter_SSs]

I'd be interested to know what values [@4] might be other than those listed: r611, r612, r711 etc

 

[go14344]

I'd be interested to know what values [@4] might be other than those listed: r611, r612, r711 etc

as of now thats all.. but plan to add another 2 values.. if [@4]=r53011 then 12330, if r53012 then 1330

 

[Peter_SSs]

as of now thats all.. but plan to add another 2 values.. if [@4]=r53011 then 12330, if r53012 then 1330

Well if @4 cannot be anything else (eg blank, "xxx", "R9999" etc) and those 2 blue values you really meant "r12330" and "r1330" then you could certainly create a lookup table and use VLOOKUP as suggested by jimrward or you could effectively do the same thing but all within a formula like this

=LEFT([@4],1)&INDEX({3,124,14,125,15,15,2630,12530,1530,12330,1330},MATCH(REPLACE([@4],1,1,"")+0,{7,611,612,711,712,812,9301,73011,73012,53011,53012},0))