Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup multiple columns | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
lookup a value from multiple columns of two workbooks | Excel Worksheet Functions | |||
lookup across multiple columns | Excel Worksheet Functions | |||
Lookup + Sum multiple columns | Excel Worksheet Functions |