Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP AND N/A ERRORS
I am trying to enter a formula into cell that included the VLOOKUP function
and I keep getting an N/A error as my result. Why would it do this, and what can I do to ensure I don't keep getting it? I am in worksheet and I am basically trying to reference another worksheet for some of the values. I enter C3 as my lookup value (in existing sheet); I enter a range reference (from another worksheet) as my table array; I enter the number 2 because that is column (from the another worksheet) that I want to use; finally, I enter FALSE because I want to return an exact match). I also have to multiply the lookup value by C4 (in existing sheet). Any advise is greatly appreciated. Thanks. Amy -- Amy Howell |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP AND N/A ERRORS
That suggests that you aren't finding the lookup value. Maybe that, or the
values in the table have leading or trailing spaces. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amy howell" wrote in message ... I am trying to enter a formula into cell that included the VLOOKUP function and I keep getting an N/A error as my result. Why would it do this, and what can I do to ensure I don't keep getting it? I am in worksheet and I am basically trying to reference another worksheet for some of the values. I enter C3 as my lookup value (in existing sheet); I enter a range reference (from another worksheet) as my table array; I enter the number 2 because that is column (from the another worksheet) that I want to use; finally, I enter FALSE because I want to return an exact match). I also have to multiply the lookup value by C4 (in existing sheet). Any advise is greatly appreciated. Thanks. Amy -- Amy Howell |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP AND N/A ERRORS
On Sun, 30 Mar 2008 14:22:00 -0700, amy howell
wrote: I am trying to enter a formula into cell that included the VLOOKUP function and I keep getting an N/A error as my result. Why would it do this, and what can I do to ensure I don't keep getting it? I am in worksheet and I am basically trying to reference another worksheet for some of the values. I enter C3 as my lookup value (in existing sheet); I enter a range reference (from another worksheet) as my table array; I enter the number 2 because that is column (from the another worksheet) that I want to use; finally, I enter FALSE because I want to return an exact match). I also have to multiply the lookup value by C4 (in existing sheet). Any advise is greatly appreciated. Thanks. Amy It would be easier if you copied the formula that gave the N/A error. Make sure that the range that you refer to includes also the second column. If not, that would be one source of the N/A error. Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP AND N/A ERRORS
Hi Amy,
My standard answer: ================================================== ======= Vlookup gives wrong answer Niek Otten, April 1 2006 Frequent causes: 1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function. Check both the search arguments and the lookup table. Formatting as numbers afterwards doesn't help. Remedy: Format an empty cell as Number. Enter the number 1. EditCopy. Select your "numbers". EditPaste Special, check Multiply. 2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted. 3. There are spaces or other invisible characters in either the search arguments or the lookup table. This often happens when you import data from other applications. Use the LEN() function to see how many characters there really are in the cell and compare that with what you see. Use the TRIM function to remove all spaces except single spaces between words. Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David McRitchie, which can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall 4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in the Paste process and now point to the wrong range. Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20. When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones (normally only for the table, not for the search argument, but this depends on your specific problem). The first hit of F4 changes the default relative address to an absolute one. That is usually what you need. Even better: use a Defined Name for the table instead of cell addresses; InsertNameDefine. 5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was not adjusted accordingly. To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary) and insert new rows or cells between those two. Then the definition of the range or the Defined Name will adjust automatically. Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables. ================================================== ======= -- Kind regards, Niek Otten Microsoft MVP - Excel "amy howell" wrote in message ... |I am trying to enter a formula into cell that included the VLOOKUP function | and I keep getting an N/A error as my result. Why would it do this, and what | can I do to ensure I don't keep getting it? | | I am in worksheet and I am basically trying to reference another worksheet | for some of the values. I enter C3 as my lookup value (in existing sheet); I | enter a range reference (from another worksheet) as my table array; I enter | the number 2 because that is column (from the another worksheet) that I want | to use; finally, I enter FALSE because I want to return an exact match). I | also have to multiply the lookup value by C4 (in existing sheet). | | Any advise is greatly appreciated. Thanks. | Amy | -- | Amy Howell |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP AND N/A ERRORS
i don't have that
-- Amy Howell "Bob Phillips" wrote: That suggests that you aren't finding the lookup value. Maybe that, or the values in the table have leading or trailing spaces. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "amy howell" wrote in message ... I am trying to enter a formula into cell that included the VLOOKUP function and I keep getting an N/A error as my result. Why would it do this, and what can I do to ensure I don't keep getting it? I am in worksheet and I am basically trying to reference another worksheet for some of the values. I enter C3 as my lookup value (in existing sheet); I enter a range reference (from another worksheet) as my table array; I enter the number 2 because that is column (from the another worksheet) that I want to use; finally, I enter FALSE because I want to return an exact match). I also have to multiply the lookup value by C4 (in existing sheet). Any advise is greatly appreciated. Thanks. Amy -- Amy Howell |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP AND N/A ERRORS
what happens when i copy the formula? yes, i did enter the number 2 as my
column index number, i dont think that is the issue though. -- Amy Howell "Lars-Ã…ke Aspelin" wrote: On Sun, 30 Mar 2008 14:22:00 -0700, amy howell wrote: I am trying to enter a formula into cell that included the VLOOKUP function and I keep getting an N/A error as my result. Why would it do this, and what can I do to ensure I don't keep getting it? I am in worksheet and I am basically trying to reference another worksheet for some of the values. I enter C3 as my lookup value (in existing sheet); I enter a range reference (from another worksheet) as my table array; I enter the number 2 because that is column (from the another worksheet) that I want to use; finally, I enter FALSE because I want to return an exact match). I also have to multiply the lookup value by C4 (in existing sheet). Any advise is greatly appreciated. Thanks. Amy It would be easier if you copied the formula that gave the N/A error. Make sure that the range that you refer to includes also the second column. If not, that would be one source of the N/A error. Hope this helps / Lars-Ã…ke |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP AND N/A ERRORS
Lars-Ake meant for you to copy and Post the formula and are you sure the number
2 is a "number". Perhaps it is text. Gord Dibben MS Excel MVP On Sun, 30 Mar 2008 17:14:00 -0700, amy howell wrote: what happens when i copy the formula? yes, i did enter the number 2 as my column index number, i dont think that is the issue though. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup returning Errors (ISNA formula) | Excel Worksheet Functions | |||
VLOOKUP returning errors | Excel Worksheet Functions | |||
vlookup, but ignore errors if #n/a | Excel Worksheet Functions | |||
VLookup Errors | Excel Worksheet Functions | |||
errors using Vlookup | Excel Worksheet Functions |