Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default lookup using multiple columns

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default lookup using multiple columns

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mario" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default lookup using multiple columns

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/

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default lookup using multiple columns

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 !
-----------------------------------------------------------------------------------------------

"Mike Anas" wrote in message
...
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/


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default lookup using multiple columns

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


"mario" wrote:

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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default lookup using multiple columns

On Dec 15, 10:41 am, "RagDyeR" wrote:
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 !
------------------------------------------------------------------------------------------------

"Mike Anas" wrote in message

...
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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default lookup using multiple columns

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

abcd & efgh
AND
abc & defgh

Will incorrectly be returned as a match.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike Anas" wrote in message
...
On Dec 15, 10:41 am, "RagDyeR" wrote:
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 !


--------------------------------------------------------------------------

----------------------

"Mike Anas" wrote in message


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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default lookup using multiple columns

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

=A1&"|"&B1

abcd|efgh

--

Regards
Roger Govier

"Ragdyer" wrote in message
...
One of the problems using concatenation with these types of lookups is:

abcd & efgh
AND
abc & defgh

Will incorrectly be returned as a match.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike Anas" wrote in message
...
On Dec 15, 10:41 am, "RagDyeR" wrote:
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 !


--------------------------------------------------------------------------

----------------------

"Mike Anas" wrote in message


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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup multiple columns TJ[_2_] Excel Worksheet Functions 2 March 19th 07 06:29 PM
Lookup in Multiple Columns, Return Multiple Values andy62 Excel Worksheet Functions 3 July 6th 06 02:36 AM
lookup a value from multiple columns of two workbooks Asad - Shareef Excel Worksheet Functions 2 May 9th 06 11:10 PM
lookup across multiple columns NHP Excel Worksheet Functions 4 March 31st 06 10:31 PM
Lookup + Sum multiple columns olasa Excel Worksheet Functions 0 May 24th 05 12:07 AM


All times are GMT +1. The time now is 12:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"