# Excel Nested IF/Vlookup

March 10, 2005 at 12:19:17
Specs: Win 2003, P3 and 256K

 Hope somebody can help. This relates to a pricing spreadsheetI have a price list for a software license that varies depending on the qty of licenses. 1-4 cost 1000; 5-9 cost 800 and so on.I have a cell with a number of licenses that a client requires. This cell can vary.I have another cell which will show the cost of the licenses.I would like to put a formula in this cell that looks ate the number of licenses required and applies the corrct license rate.What is the best method for doing this? An IF statement or a Vlookup table?I am sort of familiar with easy IF statements but I can't get this one to work. I can get answers to the TRUE FALSE statements but no formula result in the cell. I'm obviously doing something stupid. Or maybe a Vlookup is the best solution.Any help most welcome.John

See More: Excel Nested IF/Vlookup

#1
March 10, 2005 at 16:27:44

 =IF(AND((B1>=VALUE(LEFT(F1,1))),(B1<=VALUE(RIGHT(F1,1)))),G1,IF(AND((B1>=VALUE(LEFT(F2,1))),(B1<=VALUE(RIGHT(F2,1)))),G2,IF(AND((B1>=VALUE(LEFT(F3,2))),(B1<=VALUE(RIGHT(F3,2)))),G3,G4)))This is for a list of quantities in column F, as 1-4, 5-9, 10-15, 16-20and price in column G. Lookup value in B1.

Report •

#2
March 11, 2005 at 08:35:32

 Many thanks!! If I wanted to add additional quantity in column F and corresponding price how would this impact on the statement?? I just need a 50+ qty. Would it look like this=IF(AND((B1>=VALUE(LEFT(F1,1))),(B1<=VALUE(RIGHT(F1,1)))),G1,IF(AND((B1>=VALUE(LEFT(F2,1))),(B1<=VALUE(RIGHT(F2,1)))),G2,IF(AND((B1>=VALUE(LEFT(F3,2))),(B1<=VALUE(RIGHT(F3,2)))),G3,IF(AND((B1>=VALUE(LEFT(F4,3)),(B1<=VALUE(left(F4,3))))),G4,54)))You have saved my sanity:)John

Report •

#3
April 8, 2005 at 13:33:45

 This works if you have lest than 7 nested Ifs. That is the limit on Excel.

Report •

Related Solutions