# How to write an IF function in Excel that performs a calculation based on 3 arguments?

I have a table with three types of customers: GOV, NProfit, and COM, contained in column B. Each of these receives a different billing rate, 1.50, 2, and 3 respectively which needs to be applied to units billed in column G.

I need to write a function in column H that reads the type of customer in column B, and applies the appropriate rate for the customer type to the units billed in column G, giving me the total bill in column H.

-

Although tempting at first sight, nested IF() functions are not the best way to handle this scenario.

For ease of maintenance I'd strongly suggest to build a separate table with the three customer types and their respective billing rates. A simple table with two columns will do. Column A is customer type, column B is billing rate.

Then you can use a VLookup to get the billing rate for the customer type.

The benefit of this approach is that if the billing rate changes, you won't have to change all the cells with nested IF statements as suggested above. You just change the billing rate in the lookup table.

Place the lookup table into a new sheet called, say "Lookup". Row 1 is labels. A1 is Customer type, B1 is Rate.

On your original data sheet you can use a Vlookup like this

``````=VLOOKUP(A1,Lookup!\$A\$1:\$B\$4,2,FALSE)
``````

This Lookup assumes that the customer type is in cell A1 on the current sheet. Please adjust to your scenario. Also, you can nest the Vlookup into another calculation. For example, if you want to do a calculation that involves the billing rate, you can do something like

``````=B1*VLOOKUP(A1,Lookup!\$A\$1:\$B\$4,2,FALSE)
``````

where A1 of the current sheet is the customer type and B1 of the current sheet is the value you want to multiply with the billing rate of the respective customer type.

If your data may not always have a customer type, you may want to use some error trapping for data without a customer type. Normally, the formula would return an error message. If you wrap the formula in an IFError() statement, you can flag these, like

``````=IFERROR(B1*VLOOKUP(A1,Lookup!\$A\$1:\$B\$4,2,FALSE),"Please check data!")
``````
-
I only have one column for all those different arguments though. Is that means I need insert multiple VLOOKUP functions? – Chad Pranke Feb 17 '13 at 18:51
Not sure I understand. Can you explain about the "one column"? – teylyn Feb 17 '13 at 23:31

Are you referring to something like this?

=IF(B1=1,"customer1",IF(B1=2,"customer2",IF(B1=3,"customer3","invalid option")))

Replace 1, 2, 3 with the actual value you are "testing" and the "customer1", "customer2", "customer3" with what you want the output to be

-