Excel Lookup with Double Criteria

[philwes83170]

I have two sheets, one contains data imported from a database the second will display parts of the data.

Sheet 1 looks something like this:

Name Qtr Amount
Smith Q1 1000
Smith Q2 1000
Jones Q1 800
Jones Q2 500

Sheet 2 looks like this:

Qtr1 Qtr2
Smith
Jones

I'd like to get a lookup to find the values from sheet one, but can't seem to find a function that will do an "AND". If I could get something like this VLOOKUP(A2 and "Q1", sheet1!table, 3)

Thanks in advance for your help.

 

[Lilliabeth]

How about a pivot table?

 

[philwes83170]

Nope... picky end users....

 

[AnotherHiggins]

You could use a 'helper column' on sheet1 with the name and QTR concatenated (=A2&B2). Then use a VLookup like
[COLOR=blue white]=VLOOKUP(A2&B1,Sheet1!table,2,0)[/color]

OR

you could use SumProduct to find the results you seek:
[COLOR=blue white]=SUMPRODUCT((Sheet1!Names=A2)*(Sheet1!Qtr=B1)*(Sheet1!Amount))[/color]

Or, as Lilliabeth said, a pivot table would work, too.

Why do the end users care how to get the results, as long as they are displayed correctly?

[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.