Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup not working
I am trying to do a VLOOKUP so that the FAR column from TABLE 2 gets filled
into the FAR column of TABLE 1. I keep getting the N/A error. I tried sorting and changing text to numbers and vice versa. I had them on separate workbooks but now i placed both tables on the same sheet hoping for a difference. It is a very long list of 6333 names on the first table and 7227 names on the second table. Below is the formula i used in cell F2 with a sample of 3. =VLOOKUP(B2,H1:M7227,6,FALSE) TABLE 1 A B C D E F School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 #N/A 302 10009557 Cat Jessica 12 #N/A 302 10011618 Smith Paloma 12 #N/A TABLE 2 H I J K L M School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 N 302 10009557 Cat Jessica 12 N 302 10011597 Smith Robert 12 Y |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup not working
The big issue is that the table (H1:M7227 in your case) needs to have the key
value you're looking for in the first column. Since you're looking up the PermID, change the table_range to start in column I. Once you do that, you also need to change the column_no to 5. Finally, you probably want to use absolute references for the table. The result: =VLOOKUP(B2,$I$1:$M$7227,5,FALSE). "Juan" wrote: I am trying to do a VLOOKUP so that the FAR column from TABLE 2 gets filled into the FAR column of TABLE 1. I keep getting the N/A error. I tried sorting and changing text to numbers and vice versa. I had them on separate workbooks but now i placed both tables on the same sheet hoping for a difference. It is a very long list of 6333 names on the first table and 7227 names on the second table. Below is the formula i used in cell F2 with a sample of 3. =VLOOKUP(B2,H1:M7227,6,FALSE) TABLE 1 A B C D E F School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 #N/A 302 10009557 Cat Jessica 12 #N/A 302 10011618 Smith Paloma 12 #N/A TABLE 2 H I J K L M School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 N 302 10009557 Cat Jessica 12 N 302 10011597 Smith Robert 12 Y |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup not working
Thank you very much! That worked. I didn't think it would make a difference
where the key started. Also, I guess I need to learn more about absolute references becuase I thought that by adding those, it would return the same field every time. Thanks again. "bapeltzer" wrote: The big issue is that the table (H1:M7227 in your case) needs to have the key value you're looking for in the first column. Since you're looking up the PermID, change the table_range to start in column I. Once you do that, you also need to change the column_no to 5. Finally, you probably want to use absolute references for the table. The result: =VLOOKUP(B2,$I$1:$M$7227,5,FALSE). "Juan" wrote: I am trying to do a VLOOKUP so that the FAR column from TABLE 2 gets filled into the FAR column of TABLE 1. I keep getting the N/A error. I tried sorting and changing text to numbers and vice versa. I had them on separate workbooks but now i placed both tables on the same sheet hoping for a difference. It is a very long list of 6333 names on the first table and 7227 names on the second table. Below is the formula i used in cell F2 with a sample of 3. =VLOOKUP(B2,H1:M7227,6,FALSE) TABLE 1 A B C D E F School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 #N/A 302 10009557 Cat Jessica 12 #N/A 302 10011618 Smith Paloma 12 #N/A TABLE 2 H I J K L M School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 N 302 10009557 Cat Jessica 12 N 302 10011597 Smith Robert 12 Y |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup not working
For the Table Range, you want it to be the same all the time.
For the Lookup Value.........B2..........you want it to increment as you copy down. Gord Dibben MS Excel MVP On Wed, 21 Jan 2009 16:27:00 -0800, Juan wrote: Thank you very much! That worked. I didn't think it would make a difference where the key started. Also, I guess I need to learn more about absolute references becuase I thought that by adding those, it would return the same field every time. Thanks again. "bapeltzer" wrote: The big issue is that the table (H1:M7227 in your case) needs to have the key value you're looking for in the first column. Since you're looking up the PermID, change the table_range to start in column I. Once you do that, you also need to change the column_no to 5. Finally, you probably want to use absolute references for the table. The result: =VLOOKUP(B2,$I$1:$M$7227,5,FALSE). "Juan" wrote: I am trying to do a VLOOKUP so that the FAR column from TABLE 2 gets filled into the FAR column of TABLE 1. I keep getting the N/A error. I tried sorting and changing text to numbers and vice versa. I had them on separate workbooks but now i placed both tables on the same sheet hoping for a difference. It is a very long list of 6333 names on the first table and 7227 names on the second table. Below is the formula i used in cell F2 with a sample of 3. =VLOOKUP(B2,H1:M7227,6,FALSE) TABLE 1 A B C D E F School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 #N/A 302 10009557 Cat Jessica 12 #N/A 302 10011618 Smith Paloma 12 #N/A TABLE 2 H I J K L M School Perm ID. Last Name First Name Grade FAR 327 1740304 Mouse Mauricio 12 N 302 10009557 Cat Jessica 12 N 302 10011597 Smith Robert 12 Y |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP not working | Excel Discussion (Misc queries) | |||
Vlookup not working | Excel Discussion (Misc queries) | |||
Vlookup not working | Excel Worksheet Functions | |||
Vlookup no working | New Users to Excel | |||
VLOOKUP not working | Excel Worksheet Functions |