lookup table multivalue

T

Tomo

Can lookup look at two values (in separate columns) and return the value in
the third column. e.g. I want a spreadsheet where i can input the product
type in column 1 (e.g. coke) and the size in column 2 (e.g. 1.5L) and
automatically fill the third column with the price. I'm using Excel 2003
 
T

T. Valko

One way...

A1:A10 = product name
B1:B10 = product sizes
C1:C10 = price

=SUMPRODUCT(--(A1:A10="coke"),--(B1:B10="1.5L"),C1:C10)

Better to use cells to hold the criteria:

E1 = coke
F1 = 1.5L

=SUMPRODUCT(--(A1:A10=E1),--(B1:B10=F1),C1:C10)
 
Top