Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLookup Function
Hi,
I'm trying to lookup a certain column of data in another spreadsheet based on values that match (empnames) in both spreadsheets, and put those values into my master spreadsheet I have. The syntax: VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) I assume that "lookup_value" is that first column in the master spreadsheet (empnames); table_array is the column that houses the values that is in the other spreadsheet that I want in the Master one; col_index_num is the number of the column that houses that data. Is this correct? I'm getting an error saying "#N/A" What am I doing wrong? Thanks, MN |
#2
|
|||
|
|||
col-index is not the number of columns, but rather the column index that you
are interested in. So if the lookup table is of the format empnames, fullname, age, sex, etc, and you want fullname then col_index would be 2. Also lookup is set to false if you want an exact match. -- HTH RP (remove nothere from the email address if mailing direct) "MacNut" wrote in message ... Hi, I'm trying to lookup a certain column of data in another spreadsheet based on values that match (empnames) in both spreadsheets, and put those values into my master spreadsheet I have. The syntax: VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) I assume that "lookup_value" is that first column in the master spreadsheet (empnames); table_array is the column that houses the values that is in the other spreadsheet that I want in the Master one; col_index_num is the number of the column that houses that data. Is this correct? I'm getting an error saying "#N/A" What am I doing wrong? Thanks, MN |
#3
|
|||
|
|||
Thanks for your response. Yes, I did do that. I put the number of that
particular column that houses that data i'm after. I also put "false" at the end as well....still getting either a N/A error! What else am I doing wrong? For Table_Array do I need to select the entire spreadsheet range or just the particular column i'm matching up lookup_value to? "Bob Phillips" wrote: col-index is not the number of columns, but rather the column index that you are interested in. So if the lookup table is of the format empnames, fullname, age, sex, etc, and you want fullname then col_index would be 2. Also lookup is set to false if you want an exact match. -- HTH RP (remove nothere from the email address if mailing direct) "MacNut" wrote in message ... Hi, I'm trying to lookup a certain column of data in another spreadsheet based on values that match (empnames) in both spreadsheets, and put those values into my master spreadsheet I have. The syntax: VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) I assume that "lookup_value" is that first column in the master spreadsheet (empnames); table_array is the column that houses the values that is in the other spreadsheet that I want in the Master one; col_index_num is the number of the column that houses that data. Is this correct? I'm getting an error saying "#N/A" What am I doing wrong? Thanks, MN |
#4
|
|||
|
|||
Table array should be the whole table that you are referring across to, all
rows, and all columns, and the first column should be the empnames that you are comparing against. Oh, don't forget to include the sheet name. SO all it should look something like =VLOOKUP(A1,'Sheet name'!$A$1:$H$20,2,FALSE) -- HTH RP (remove nothere from the email address if mailing direct) "MacNut" wrote in message ... Thanks for your response. Yes, I did do that. I put the number of that particular column that houses that data i'm after. I also put "false" at the end as well....still getting either a N/A error! What else am I doing wrong? For Table_Array do I need to select the entire spreadsheet range or just the particular column i'm matching up lookup_value to? "Bob Phillips" wrote: col-index is not the number of columns, but rather the column index that you are interested in. So if the lookup table is of the format empnames, fullname, age, sex, etc, and you want fullname then col_index would be 2. Also lookup is set to false if you want an exact match. -- HTH RP (remove nothere from the email address if mailing direct) "MacNut" wrote in message ... Hi, I'm trying to lookup a certain column of data in another spreadsheet based on values that match (empnames) in both spreadsheets, and put those values into my master spreadsheet I have. The syntax: VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) I assume that "lookup_value" is that first column in the master spreadsheet (empnames); table_array is the column that houses the values that is in the other spreadsheet that I want in the Master one; col_index_num is the number of the column that houses that data. Is this correct? I'm getting an error saying "#N/A" What am I doing wrong? Thanks, MN |
#5
|
|||
|
|||
Excellent! So that was what I was doing wrong, I wasn't using the WHOLE SHEET
as a reference - i was just using the column that it was in. I also needed to make sure EmpNames was the first column as well. Thanks so much for all your help! MN "Bob Phillips" wrote: Table array should be the whole table that you are referring across to, all rows, and all columns, and the first column should be the empnames that you are comparing against. Oh, don't forget to include the sheet name. SO all it should look something like =VLOOKUP(A1,'Sheet name'!$A$1:$H$20,2,FALSE) -- HTH RP (remove nothere from the email address if mailing direct) "MacNut" wrote in message ... Thanks for your response. Yes, I did do that. I put the number of that particular column that houses that data i'm after. I also put "false" at the end as well....still getting either a N/A error! What else am I doing wrong? For Table_Array do I need to select the entire spreadsheet range or just the particular column i'm matching up lookup_value to? "Bob Phillips" wrote: col-index is not the number of columns, but rather the column index that you are interested in. So if the lookup table is of the format empnames, fullname, age, sex, etc, and you want fullname then col_index would be 2. Also lookup is set to false if you want an exact match. -- HTH RP (remove nothere from the email address if mailing direct) "MacNut" wrote in message ... Hi, I'm trying to lookup a certain column of data in another spreadsheet based on values that match (empnames) in both spreadsheets, and put those values into my master spreadsheet I have. The syntax: VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) I assume that "lookup_value" is that first column in the master spreadsheet (empnames); table_array is the column that houses the values that is in the other spreadsheet that I want in the Master one; col_index_num is the number of the column that houses that data. Is this correct? I'm getting an error saying "#N/A" What am I doing wrong? Thanks, MN |
#6
|
|||
|
|||
Hi
An example: You have a sheet Employees with columns: Empname, Occupation, BirthDate, Sex with data in range A2:D100, and with a row for every employee. On other sheet, in cell A2 you have employees name, listed in sheet Employees, and you want to retrieve p.e. employees birthday. The formula will be: =VLOOKUP(A1,Employees!$A$2:$D$100,3,0) I.e. you look for exact match (4th parameter is 0 or FALSE) of value from cell A1 (1st parameter) in range Employees!A2:D100 (2nd parameter), and return value from 3rd column of lookup range (3rd parameter - points to column BirthDate) on same row of lookup range. Arvi Laanemets "MacNut" wrote in message ... Hi, I'm trying to lookup a certain column of data in another spreadsheet based on values that match (empnames) in both spreadsheets, and put those values into my master spreadsheet I have. The syntax: VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) I assume that "lookup_value" is that first column in the master spreadsheet (empnames); table_array is the column that houses the values that is in the other spreadsheet that I want in the Master one; col_index_num is the number of the column that houses that data. Is this correct? I'm getting an error saying "#N/A" What am I doing wrong? Thanks, MN |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
carrying a hyper link when using the vlookup function | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |