Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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!")
share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.