Nested If - use lookup instead?

hedgie

Board Regular
Joined
Jun 23, 2004
Messages
160
I can't seem to find this in the archives.

I have a complicated Nested If statement that basically says the following:

=if(a1="Multiply", b1*d1,if(a1="divide", b1/e1, if(a1="add", b1+f1,b1),c1),0),0)

As the spreadsheet grows, the nesting is growing too. I would like to make a database table which would have the ability to add conditions.

Multiply column 2 * column 4
Divide Column 2 / column 5 etc...


thsi way I can use a simple vlookup and then have a formula applied. Is there a way of using Index and match to accomplish this?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Perhaps this?
Book4
ABCDEF
1Input1Input2OperationSymbolResult
2102Multiply*20
3Divide/5
4Add+12
5Subtract-5
6Exponent^100
7
8OperationSelectedDivide
9Result5
10Formula=10/2
Sheet1
 

hedgie

Board Regular
Joined
Jun 23, 2004
Messages
160
Maybe I confused the issue. The operations are not always perfromed on the same numbers. Basically, I want to create a rule table that can be used to look up what to do in any given row. (I cannot add a sample spreadsheet unfortunately, so hopefully this will makes sense)

Each row has data that includes the type of instrument. (ie. a bond, stock or cash) If the item is a "bond" then i need to take the column that has the cost and multiply it by say .05. If it is a Stock, then you take the column that has the market value (not the same column as the cost) and multiply it by the column that has the number of shares. If it is cash then the cell should be blank. Rather than create a nested if statement to do this, I want to have a table which outlines these rules and then allows me to apply them.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994

ADVERTISEMENT

Thanks, HalfAce. I was trying to do some sort of =formulaastext(A1&VLOOKUP(B2,{"multiply","*";"divide","/";"add","+";"subtract","-";"exponent","^"},2,0)&A2), but gave up on that idea...
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
hedgie, per your second question, perhaps my last post would help you on your quest.

You could do something like =VLOOKUP(B2,{"stock",C2*D2;"bond",E2*.05;"cash",""},2,0)
 

hedgie

Board Regular
Joined
Jun 23, 2004
Messages
160

ADVERTISEMENT

I am confused by your last formula. It seems to be ommitting the table array that it needs to look up the "stock" "bond" and "cash". Is there an explanation of using arrays within vlookup somewhere I can reference?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
I think that, all you really need to know is:

commas separate columns and semicolons separate rows.

So, {"stock",C2*D2;"bond",E2*.05;"cash",""} is the equivalent of
Stock || C2*D2
Bond || D2*.05
Cash || ""

Where || denotes a new cell
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Here is a UDF that works with a slight mod to your specs:

Code:
Function func(a As Variant, b As Variant, c As String)
    Dim myString as String
    myString = a & c & b
    func = Evaluate(myString)
End Function

Try this =func(A1,B1,"*") to multiply the two cells. You could modify the function to determine the operator based on "multiply" instead if so inclined.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
It is unclear how far you want to go. However, take note of the fact that one cannot "write up" a formula parser by means of formulas, which would allow you to work with variables that you can bind dynamically and evaluate to a result.

Some things, though limited in scope, can be done like:

1]

=CHOOSE(MATCH(A1,{"Multiply","Divide","Add"},0),B1*D1,B1/E1,B1+F1)

2]

=EVAL(B1&LOOKUP(A1,{"Add","+";"Divide","/";"Multiply","*";"Substract","-"})&D1)

Maybe you have a specific, well-defined set of operations which can be expressed in terms of EVAL and a lookup table...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,478
Messages
5,601,900
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top