IF statement help

[HERZHIS]

I have a Dependent validation data that when the dependent is chosen it
populates cost in a cell
example:
B1-type of class (basic,standard,advanced)
C1-list of classes per type selected
D1-cost of class in C1

Choices
Basic has 10 classes: 7= $100, 2 = 150 and 1= 200.00
Standard has 4 classes all cost 1600.00
Advanced has 6 classes all cost 1600.00
My IF statement doesn't work since there is more that 7
What next?!?!?!
-
Thanks again, Herz and His

 

[T. Valko]

Since the price is the same for class types of standard and advanced all you
need to do is test cell B1 for those types to get the price. You'd need to
create a 2 column table for the basic class type. In the left column of this
table you'd list the class names/designations and in the right column you'd
list the corresponding price. Then your formula might look like this:

=IF(OR(B1={"Standard","Advanced"}),1600,IF(AND(B1="Basic",ISNA(MATCH(C1,H2:I11,0))),"",IF(B1="Basic",VLOOKUP(C1,H2:I11,2,0),"")))

Where H2:I11 = the 2 column table for the basic classes.

 

[Stephane Quenson]

Herz,

I would use the VLOOKUP function with a little trick to allow it for search
with two arguments.

First, I would make a table with all the possible combinations, in your case
10+4+6 rows. Let's say you have Type in column A, class in column B, then add
a calculated column in C with the formula in C2: = A2&B2, then column D with
the cost.

Then, in the area where you want to automatically display the cost, use the
formula: =VLOOKUP(F8&G8,C219,2,0), where F8 is the cell containing the type
and G8 the cell containing the class. C219 represents the range with Type &
Class, costs.

Stephane.