Nested If - use lookup instead?

hedgie

Board Regular
Joined
Jun 23, 2004
Messages
174
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Perhaps this?
Book4
ABCDEF
1Input1Input2OperationSymbolResult
2102Multiply*20
3Divide/5
4Add+12
5Subtract-5
6Exponent^100
7
8OperationSelectedDivide
9Result5
10Formula=10/2
Sheet1
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top