lookup using multiple columns

[mario]

I have spreadsheet 1, like this:

Student Name Semester GPA
David Spring 3.8
David Summer 3.9
David Fall 3.5
David Winter 3.3
Manny Spring 2.1
Manny Summer 2.2
Manny Fall 2.5
Manny Winter 2.8


I have another different spread 2, like this:

StudentName Semester GPA
Manny Summer
David Fall

For the spreadsheet 2, I want to look up the values for GPA using
spreadsheet 1.

Is there a way I can do this excel.

Pls help.

 

[RagDyer]

On Sheet1, Name in Column A, Semester in Column B, GPA in Column C.

On Sheet2, *same* configuration!

Try this is C2 of Sheet2:

=SUMPRODUCT((Sheet1!A$2:A$9=A2)*(Sheet1!B$2:B$9=B2)*Sheet1!C$2:C$9)

And copy down as needed.

 

[Mike Anas]

I recommend using the VLOOKUP worksheet function. If spreadsheet 1
occupied the range A1:C9 of sheet1, then you could place the following
formula in spreadsheet 2.

=VLOOKUP("Manny",sheet1!A1:C9,3,false)

If "Manny", is in cell A1, it would read:

=VLOOKUP(A1,sheet1!A1:C9,3,false)

The third parameter, the 3 above, indicates which column to pull the
data from when the name is matched. The false parameter indicates an
exact match must be found. In plain English, this formula is saying,
find the value in cell A1 in the first column of the range A1:C9 on
sheet1. When there is a match, and only an exact match, pull the value
from column 3 of the same row in that range.

Mike Anas
http://mikeanas.googlepages.com/

 

[RagDyeR]

How would your Vlookup suggestion choose *which semester* of "Manny" to
return?
--

Regards,

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

I recommend using the VLOOKUP worksheet function. If spreadsheet 1
occupied the range A1:C9 of sheet1, then you could place the following
formula in spreadsheet 2.

=VLOOKUP("Manny",sheet1!A1:C9,3,false)

If "Manny", is in cell A1, it would read:

=VLOOKUP(A1,sheet1!A1:C9,3,false)

The third parameter, the 3 above, indicates which column to pull the
data from when the name is matched. The false parameter indicates an
exact match must be found. In plain English, this formula is saying,
find the value in cell A1 in the first column of the range A1:C9 on
sheet1. When there is a match, and only an exact match, pull the value
from column 3 of the same row in that range.

Mike Anas
http://mikeanas.googlepages.com/

 

[Teethless mama]

In Sheet 2:
A2: Holds name
B2: Holds Semester

C2: =INDEX(GPA,INDEX(MATCH(A2&B2,Student_Name&Semester,0),0))
copy down as far as you need

 

[Mike Anas]

How would your Vlookup suggestion choose *which semester* of "Manny" to
return?
--

Regards,

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


I recommend using the VLOOKUP worksheet function. If spreadsheet 1
occupied the range A1:C9 of sheet1, then you could place the following
formula in spreadsheet 2.

=VLOOKUP("Manny",sheet1!A1:C9,3,false)

If "Manny", is in cell A1, it would read:

=VLOOKUP(A1,sheet1!A1:C9,3,false)

The third parameter, the 3 above, indicates which column to pull the
data from when the name is matched. The false parameter indicates an
exact match must be found. In plain English, this formula is saying,
find the value in cell A1 in the first column of the range A1:C9 on
sheet1. When there is a match, and only an exact match, pull the value
from column 3 of the same row in that range.

Mike Anashttp://mikeanas.googlepages.com/

RD- you are right to point that out, that's why I pulled my post. I
didn't catch that this was a 2-field lookup. Normally, I handle these
types of situations by creating an extra field that concatenates the
two, and then do a VLOOKUP on that.

Mike

 

[Ragdyer]

One of the problems using concatenation with these types of lookups is:

abcd & efgh
AND
abc & defgh

Will incorrectly be returned as a match.

 

[Roger Govier]

Unless you use a separator.
For this type of concatenation I always include the pipe character |

=A1&"|"&B1

abcd|efgh