Lookup based on criteria in 2 columns

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,

0
Mike1154 (1216)
9/29/2008 8:31:00 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
671 Views

Similar Articles

[PageSpeed] 37

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

-- 


Regards,


Peo Sjoblom

"Mike" <Mike@discussions.microsoft.com> wrote in message 
news:8F714178-FE2A-4808-92C1-54B8737B4263@microsoft.com...
> 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,
> 


0
peo.sjoblom (169)
9/29/2008 8:51:08 PM
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)

-- 
Thanks,
Shane Devenshire


"Mike" wrote:

> 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,
> 
0
9/29/2008 9:48:02 PM
Reply:

Similar Artilces: