Huge Nesting if, Convert to Macro?

[dveca]

Hi, I'm self taught on Excel, so most of my solutions are not exactly beautiful simple fixes, but I have one I can't get around now.

I have a nesting if function that is accomplishing a good chunk of computing for me.

So to start, there's 39 labels that I want to check.
Some of the data I want to bring back is in a table on a different tab, and some is a basic formula.

so for instance

=if(a1="text1",vlookup("text1", table,9,false),if(a1="text2",a3/a4*a5),if(a1="text3",sum(vlookup("text 3",table,9,false),vlookup("textsup",table,9,false)

and it goes on like that. those are the three calculations I have nested for the if statements to return.

is there macro solution, or a more elegant formula to use?

thanks

Dave

 

[Gerald Higgins]

At a very basic level, the "sum" in your formula doesn't seem to be doing anything, you can remove it.

You could also consider doing a simple VLOOKUP against A1, and then letting the formula deal with examples where a1 is not found in the table, like this

If using Excel 2007 or later...
=iferror(vlookup(a1,table,9,false),[what to do in the event of error])

If using Excel 2003 or earlier
=if(iserror(vlookup(a1,table,9,false)),[what to do in the event of error],vlookup(a1,table,9,false))

In either case, replace [what to do in the event of error] with whatever you want to do if the value in a1 is not found in the table. This could be quite a complex formula in itself.

 

[West Man]

You mention that you have 39 labels you wish to check and will then have calculations for eash. Are the calculations unique for each label or is there a smaller number than 39? If smaller, how many unique calculations?