Lookup based on criteria in 2 columns

[Mike]

Hi,
I am trying to use a vlookup or other function to return the value in the
amount column based on the location and date. Here is a sample of my data:

Location Date Amount
101 9/15/8 10
101 9/16/8 20
101 9/17/8 15
102 9/15/8 50
102 9/16/8 75
102 9/17/8 67

For example if I wanted to return the amount for location 102 on 9/15/8,
what formula would I use? I tried using variations of vlookups but had no
luck.

Thanks,

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50)

--


Regards,


Peo Sjoblom

 

[ShaneDevenshire]

Hi,

In the following examples my data only goes down to row 7 but you just need
to adjust the formula for your range.

You can array enter the following formula:

=INDEX(C2:C7,MATCH(E1&F1,A2:A7&B2:B7,0),)

If you enter the Location in E1 and the date in F1. To array enter it you
press Shift+Ctrl+Enter rather than enter.

In 2007 you could use

=SUMIFS(C2:C7,A2:A7,E1,B2:B7,F1)

WARNING: If the location is entered as text and not a number in column A
then these two formulas will work but

=SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50)

will return 0. To get it to work in that case change the formula to read

=SUMPRODUCT(--(A2:A50="102"),--(B2:B50=DATE(2008,9,15)),C2:C50)