Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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?????? |
#2
|
|||
|
|||
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?????? |
#3
|
|||
|
|||
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?????? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |