how to simplify nested if

go14344

New Member
Joined
Jun 17, 2014
Messages
26
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,755
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
I would probably use VLOOKUP create a two column list of oldvalue newvalue
R611 R124
R612 R14
=VLOOKUP([@4],rangeofyourlist)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
I'd be interested to know what values [@4] might be other than those listed: r611, r612, r711 etc
 

go14344

New Member
Joined
Jun 17, 2014
Messages
26
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,176
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top