Excel: Lookup value based on two columns

[bkrav]

I have an Excel sheet (made from an Access query) that is has three columns:
Site Date Price
1 7/24/06 9
2 7/24/06 10
3 7/24/06 4
1 7/25/06 8
2 7/25/06 3
3 7/25/06 7

I have another sheet that has all the sites listed down the left side as rows and then each date as a column:
Site 7/24 7/25
1 9 8
2 10 3
3 4 7

I would like to be able to write a function or some VBA to pull the data from the first sheet and have it arranged on the second sheet by looking up the site and the date.

Right now the way it works is that a query is ran and filtered for each day, then you have to click on each cell and 'Refresh' the data. I would like to be able to run a query for the whole month and avoid the whole clicking each cell to update fiasco. Thanks!

 

[bkrav]

I should have specified that I want to pull the Price from Sheet1, the site names and dates are constant.

Cheers.

 

[AnotherHiggins]

Have a look at Pivot Tables.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

But just to answer your original question, "Lookup value based on two columns", you could use something like:
=sumproduct((Sheet1!A1:A100=A2)*(Sheet1!B1:B100=B1))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Crud.

The formula in my last post should have been
=sumproduct((Sheet1!A1:A100=A2)*(Sheet1!B1:B100=B1)*(Sheet1!C1:C100))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bkrav]

John, I got the formula you suggested to work, however I need to be able to search from around a thousand rows, and I get a #NUM error when I try to have it search the entire column. Is it possible to search the entire column?

 

[AnotherHiggins]

No. SumProduct does not allow you to search the entire column.

But you can use (A1:A65535)* or, if you have a header row you don't need to search anyway, (A2:A65536)*.

*There are 65,536 (2[sup]16[/sup]) rows in Excel. The next release of Excel will have 1,048,576 rows (2[sup]20[/sup]).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

You could do for all versions by using some named ranges. Insert | Name | Define...

BigNum:
=9.999999999E+307

LastA:
INDEX(Sheet1!A:A,MATCH(BigNum,Sheet1!A:A))

LastB:
INDEX(Sheet1!B:B,MATCH(BigNum,Sheet1!A:A))

LastC:
INDEX(Sheet1!C:C,MATCH(BigNum,Sheet1!A:A))

Then use the formula...

=SUMPRODUCT((Sheet1!A1:LastA=A2)*(Sheet1!B1:LastB=B1)*(Sheet1!C1:LastC))

Those named ranges are all based on the last numerical value in column A.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[bkrav]

Ahhh, I tried making the range larger but I was only changing the range for the first part of the equation, not for all three parts. Thanks for the help!

 

[AnotherHiggins]

Ah.

The range in each section must contain the same number of cells.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.