Lookup using more than one Lookup value
 

I am currently using vlookup to put together a summary sheet. However, I
would like to Look up Column A and B, and then return the corresponding
Column C figure. For example:

John Brown 6
John Green 5
John Black 7

If Forename is Column A, Surname is Colume B and Column C is the value I
need to return to the summary sheet. I'm not sure how to lookup more than
one column?

Any ideas??????

Charlou,

=SUMPRODUCT(--(A1:A3="John"),--(B1:B3="Green"),C1:C3)

Or if you want to have it on another sheet:

=SUMPRODUCT(--(Sheet1!A1:A3="John"),--(Sheet1!B1:B3="Green"),Sheet1!C1:C3)

And the John and Green can also be in cells:

=SUMPRODUCT(--(Sheet1!A1:A3=A1),--(Sheet1!B1:B3=B1),Sheet1!C1:C3)
--
HTH,
Bernie
MS Excel MVP

"Charlou" wrote in message
...
I am currently using vlookup to put together a summary sheet. However, I
would like to Look up Column A and B, and then return the corresponding
Column C figure. For example:

John Brown 6
John Green 5
John Black 7

If Forename is Column A, Surname is Colume B and Column C is the value I
need to return to the summary sheet. I'm not sure how to lookup more than
one column?

Any ideas??????

Try this *array* formula:

=INDEX(Sheet1!$C$2:$C$5,MATCH(1,(Sheet1!$A$2:$A$5= A2)*(Sheet1!$B$2:$B$5=B2),
0))

Array formulas must be entered using C,S,E, <Ctrl <Shift <Enter, instead
of just Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Charlou" wrote in message
...
I am currently using vlookup to put together a summary sheet. However, I
would like to Look up Column A and B, and then return the corresponding
Column C figure. For example:

John Brown 6
John Green 5
John Black 7

If Forename is Column A, Surname is Colume B and Column C is the value I
need to return to the summary sheet. I'm not sure how to lookup more than
one column?

Any ideas??????