Lookup based on two conditions

[Harry Flashman]

I have a set of data with unique ID number and 7 brands, and each
brand has 25 attributes.
Column A consists of unique ID numbers, from 1 to 175
Columm B consists of 7 brands listed 25 times each
Column C consists of 25 attributes repeated 7 times each.

For example
A B C
1 Brand1 Attribute1
2 Brand2 Attribute2
3 Brand3 Attribute2
4 Brand1 Attribute5
etc

I would like to return to corresponding value in column A based on a
selection basded on column B and Column C.
For example, return the value of Brand1 with Attribute 5. The answer
would be 4.
The Brands and Attributes will be selected via drop down boxes (data
validation). For aruments sake let say the drop down box for the brand
will be in Cell A1, and the drop down box will be in cell A2.
The range consiting of the data will be A33:C208.

I have looked on the internet for answer but I haven't been able to
make anything work yet. I suspect I need some kind of an array
formula. Any help would be greatly appreciated.

 

[Shane Devenshire]

Hi,

If you are using 2007 then

=SUMIFS(A33:A208,B33:B208,A1,C33:C208,A2)

In this case A1 and A2 have your Brand and Attribute you are looking for.


With the 2003 version you can drop the extra parens

=SUMPRODUCT(--(A1=B33:B208),--(A2=C33:C208),A33:A208)