Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error in VLOOKUP
I am trying to get Excel to look up what is in a column in a one worksheet
and find it in another worksheet and come back with what is in a different column for the same row. Example: I have one worksheet with names in one column (column I, file 1), I want Excel to search in file 2 to find what is in column I row 2 and return what is in column DH in file 2 for that name. I keep getting either a reference error or an #NA error. Below is the formula I entered: =VLOOKUP(I20, '[Excel Report MI.xls]Data Entry'!$A$13:$DH$68, 112, FALSE) What am I doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error in VLOOKUP
Hi Kris,
Two reasons for the #N/A, or you name doesn't exist in file2 or the name is slightly different If this helps please click yes, thanks "Kris" wrote: I am trying to get Excel to look up what is in a column in a one worksheet and find it in another worksheet and come back with what is in a different column for the same row. Example: I have one worksheet with names in one column (column I, file 1), I want Excel to search in file 2 to find what is in column I row 2 and return what is in column DH in file 2 for that name. I keep getting either a reference error or an #NA error. Below is the formula I entered: =VLOOKUP(I20, '[Excel Report MI.xls]Data Entry'!$A$13:$DH$68, 112, FALSE) What am I doing wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error in VLOOKUP
Nope, the name is exactly the same, spacing and everything. Still returning
the #NA reference. "Eduardo" wrote: Hi Kris, Two reasons for the #N/A, or you name doesn't exist in file2 or the name is slightly different If this helps please click yes, thanks "Kris" wrote: I am trying to get Excel to look up what is in a column in a one worksheet and find it in another worksheet and come back with what is in a different column for the same row. Example: I have one worksheet with names in one column (column I, file 1), I want Excel to search in file 2 to find what is in column I row 2 and return what is in column DH in file 2 for that name. I keep getting either a reference error or an #NA error. Below is the formula I entered: =VLOOKUP(I20, '[Excel Report MI.xls]Data Entry'!$A$13:$DH$68, 112, FALSE) What am I doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error in VLOOKUP
Kris,
VLOOKUP looks for a value in the leftmost column of the lookup table. So if you're looking for a value in column I in file 2 your formula should be =VLOOKUP(I20, '[Excel Report MI.xls]Data Entry'!$I$13:$DH$68, 104, FALSE) Hope this helps. "Kris" wrote in message ... I am trying to get Excel to look up what is in a column in a one worksheet and find it in another worksheet and come back with what is in a different column for the same row. Example: I have one worksheet with names in one column (column I, file 1), I want Excel to search in file 2 to find what is in column I row 2 and return what is in column DH in file 2 for that name. I keep getting either a reference error or an #NA error. Below is the formula I entered: =VLOOKUP(I20, '[Excel Report MI.xls]Data Entry'!$A$13:$DH$68, 112, FALSE) What am I doing wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Error in VLOOKUP
Kris wrote...
Nope, the name is exactly the same, spacing and everything. *Still returning the #NA reference. .... Then if, say, cell '[Excel Report MI.xls]Data Entry'!A35 looked the same as cell I20 in the active workbook/worksheet, what does the formula ='[Excel Report MI.xls]Data Entry'!A35=I20 return? If it returns FALSE, what do the formulas =LEN('[Excel Report MI.xls]Data Entry'!A35) and =LEN(I20) return? If they return different numbers, then what does the formula =TRIM('[Excel Report MI.xls]Data Entry'!A35)=TRIM(I20) return? If this last formula returns TRUE, then the cell for which the LEN formulas above returned the larger number almost certainly does contain trailing spaces which you wouldn't be able to see unless you edit that cell and move the insertion point (vertical bar cursor) to the end of the cell's contents. If the first formula above returns TRUE but your formula =VLOOKUP(I20,'[Excel Report MI.xls]Data Entry'!$A$13:$DH$68,112,FALSE) returns #N/A, then you may have found a true bug in Excel unless your I20 name contains certain special characters. In which case you'd need to show us the troublesome I20 value. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup() error #n/a | Excel Worksheet Functions | |||
Vlookup and #N/A error | Excel Worksheet Functions | |||
VLookup N/A Error | Excel Discussion (Misc queries) | |||
Value Not Available Error in Vlookup | Excel Discussion (Misc queries) | |||
Vlookup Error | Excel Worksheet Functions |