Look up that fits 2 criteria

**njexpress9** · 08-25-2009, 03:45 PM

attempting (with zero luck) to populate a cell that has 2 criteria... not sure if I should use VLOOKUP or IF(SUM) or ?

I need a formula that look through a huge data set and give me a number when this AND that are equal...

Attached is a file... apologies for the confusing request.

Basically I want to populate B3 with the data that equal Type 1 and Category AA...

Thanks in advance... NJEXPRESS9

**Special-K** · 08-25-2009, 03:50 PM

Try

=SUMPRODUCT((B2=$B$13:$B$28)*(A3=$A$13:$A$28),($C$13:$C$28))

and copy across and down the entire Category matrix, ie (B3:E10)

IGNORE THE ABOVE: It doesnt work! :-)

**NBVC** · 08-25-2009, 03:50 PM

tRY, In B3,

=SUMPRODUCT(--($A$13:$A$28=$A3),--($B$13:$B$28=B$2),$C$13:$C$28)

copied down and across the table

**DonkeyOte** · 08-25-2009, 04:53 PM

If your dataset is small as per your attachment then SUMPRODUCT as already outlined is definitely an excellent way to go - if on the off chance you have masses of data in reality then I would use a helper and then SUMIF as it will be a little quicker, eg using your sample file:

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

Again, to reiterate the above is only really worthwhile if you have mass data and mass amounts of calcs to perform.

You might also want to consider using a Pivot Table though it may not give you the exact layout you desire.