Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
Hi All....
Okay.....I dont know WHY this formula is giving me #N/A. =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) I do not see anything wrong with this formula....what could cause this??? Thanks in advance Gary |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
Hi Gary,
The formula looks OK, but A1 seems not to be present in your list. Maybe one is text and the other a number; you don't see the difference, but Excel does. Use the ISNUMBER and/or the ISTEXT function to check. Use the VALUE or the TEXT function to convert one type to the other. -- Kind regards, Niek Otten Microsoft MVP - Excel "Gary" wrote in message ... | Hi All.... | | Okay.....I dont know WHY this formula is giving me #N/A. | | =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) | | I do not see anything wrong with this formula....what could cause this??? | | Thanks in advance | Gary | | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
There's nothing wrong with the formula, it must be either in the lookup
range or what is in A1. Are the values you're searching the results of formulas? If so it may well be a rounding issue, Regards, Alan. "Gary" wrote in message ... Hi All.... Okay.....I dont know WHY this formula is giving me #N/A. =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) I do not see anything wrong with this formula....what could cause this??? Thanks in advance Gary |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
There are no formulas....they are simply names and i need to lookup the
process they are working for. thats it......and its not that A1 is not there..it is there. even if i drag it down...it shows NA in all the cells... and theres one new thing i just discovered. I am trying to lookup A1...the name in A1 was copied from an email body.....now if i type the same name in A1, it gives me the result.....but i can not type it....tahts why i ma using VLOOKUP. its weird...am seeing it for the first time. please help "Alan" wrote in message ... There's nothing wrong with the formula, it must be either in the lookup range or what is in A1. Are the values you're searching the results of formulas? If so it may well be a rounding issue, Regards, Alan. "Gary" wrote in message ... Hi All.... Okay.....I dont know WHY this formula is giving me #N/A. =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) I do not see anything wrong with this formula....what could cause this??? Thanks in advance Gary |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
Hi Gary,
There may be invisible characters or spaces in your search argument. Use the CLEAN() and/or the TRIM() function and if that doesn't help, use David McRitchies TrimAll function: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Kind regards, Niek Otten Microsoft MVP - Excel "Gary" wrote in message ... | There are no formulas....they are simply names and i need to lookup the | process they are working for. thats it......and its not that A1 is not | there..it is there. even if i drag it down...it shows NA in all the cells... | | and theres one new thing i just discovered. I am trying to lookup A1...the | name in A1 was copied from an email body.....now if i type the same name in | A1, it gives me the result.....but i can not type it....tahts why i ma using | VLOOKUP. its weird...am seeing it for the first time. | please help | | "Alan" wrote in message | ... | There's nothing wrong with the formula, it must be either in the lookup | range or what is in A1. Are the values you're searching the results of | formulas? If so it may well be a rounding issue, | Regards, | Alan. | "Gary" wrote in message | ... | Hi All.... | | Okay.....I dont know WHY this formula is giving me #N/A. | | =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) | | I do not see anything wrong with this formula....what could cause this??? | | Thanks in advance | Gary | | | | |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
Hey Niek,
Whats the syntax for clean or trim? is it =CLEAN(A1) OR =TRIM(A1)? if yes, its not working. "Niek Otten" wrote in message ... Hi Gary, There may be invisible characters or spaces in your search argument. Use the CLEAN() and/or the TRIM() function and if that doesn't help, use David McRitchies TrimAll function: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Kind regards, Niek Otten Microsoft MVP - Excel "Gary" wrote in message ... | There are no formulas....they are simply names and i need to lookup the | process they are working for. thats it......and its not that A1 is not | there..it is there. even if i drag it down...it shows NA in all the cells... | | and theres one new thing i just discovered. I am trying to lookup A1...the | name in A1 was copied from an email body.....now if i type the same name in | A1, it gives me the result.....but i can not type it....tahts why i ma using | VLOOKUP. its weird...am seeing it for the first time. | please help | | "Alan" wrote in message | ... | There's nothing wrong with the formula, it must be either in the lookup | range or what is in A1. Are the values you're searching the results of | formulas? If so it may well be a rounding issue, | Regards, | Alan. | "Gary" wrote in message | ... | Hi All.... | | Okay.....I dont know WHY this formula is giving me #N/A. | | =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) | | I do not see anything wrong with this formula....what could cause this??? | | Thanks in advance | Gary | | | | |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
If this is the problem you would use
=VLOOKUP(CLEAN(A1),Sheet2!$T$2:$U$259,2,FALSE) or =VLOOKUP(TRIM(A1),Sheet2!$T$2:$U$259,2,FALSE) You say the data comes from an email, there is a space used on web sites that isn't the normal ASCII32 space that XL recognises, its ASCII160. TRIM or CLEAN wont remove these. In a cell that contains some of the data, say A1, enter =CODE(A1) which will give you the ASCII number of the first character in the cell. If it's 160 then you have the dreaded rogue space that XL doesn't recognise. To get rid of it, highlight the whole range that the copied or imported data is in, go 'Edit' 'Replace' 'Find What' - hold down the Alt key and type on the numeric keypad 0160, (nothing will be displayed in the dialogue box), 'Replace With' leave blank, hit 'Replace All' If it says that any replacements have been made then that was the problem, Regards, Alan. "Gary" wrote in message ... Hey Niek, Whats the syntax for clean or trim? is it =CLEAN(A1) OR =TRIM(A1)? if yes, its not working. "Niek Otten" wrote in message ... Hi Gary, There may be invisible characters or spaces in your search argument. Use the CLEAN() and/or the TRIM() function and if that doesn't help, use David McRitchies TrimAll function: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Kind regards, Niek Otten Microsoft MVP - Excel "Gary" wrote in message ... | There are no formulas....they are simply names and i need to lookup the | process they are working for. thats it......and its not that A1 is not | there..it is there. even if i drag it down...it shows NA in all the cells... | | and theres one new thing i just discovered. I am trying to lookup A1...the | name in A1 was copied from an email body.....now if i type the same name in | A1, it gives me the result.....but i can not type it....tahts why i ma using | VLOOKUP. its weird...am seeing it for the first time. | please help | | "Alan" wrote in message | ... | There's nothing wrong with the formula, it must be either in the lookup | range or what is in A1. Are the values you're searching the results of | formulas? If so it may well be a rounding issue, | Regards, | Alan. | "Gary" wrote in message | ... | Hi All.... | | Okay.....I dont know WHY this formula is giving me #N/A. | | =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) | | I do not see anything wrong with this formula....what could cause this??? | | Thanks in advance | Gary | | | | |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working???
Try this anyway, the space may not necessarily be the first character it's
just occurred to me, although it usually is. Finding and replacing it will do no harm even if it does no good. This same or similar thing happened to me a long time ago in a project I was involved in and it drove me mad until it was resolved, I know how you feel! Regards, Alan. To get rid of it, highlight the whole range that the copied or imported data is in, go 'Edit' 'Replace' 'Find What' - hold down the Alt key and type on the numeric keypad 0160, (nothing will be displayed in the dialogue box), 'Replace With' leave blank, hit 'Replace All' If it says that any replacements have been made then that was the problem, "Alan" wrote in message ... If this is the problem you would use =VLOOKUP(CLEAN(A1),Sheet2!$T$2:$U$259,2,FALSE) or =VLOOKUP(TRIM(A1),Sheet2!$T$2:$U$259,2,FALSE) You say the data comes from an email, there is a space used on web sites that isn't the normal ASCII32 space that XL recognises, its ASCII160. TRIM or CLEAN wont remove these. In a cell that contains some of the data, say A1, enter =CODE(A1) which will give you the ASCII number of the first character in the cell. If it's 160 then you have the dreaded rogue space that XL doesn't recognise. To get rid of it, highlight the whole range that the copied or imported data is in, go 'Edit' 'Replace' 'Find What' - hold down the Alt key and type on the numeric keypad 0160, (nothing will be displayed in the dialogue box), 'Replace With' leave blank, hit 'Replace All' If it says that any replacements have been made then that was the problem, Regards, Alan. "Gary" wrote in message ... Hey Niek, Whats the syntax for clean or trim? is it =CLEAN(A1) OR =TRIM(A1)? if yes, its not working. "Niek Otten" wrote in message ... Hi Gary, There may be invisible characters or spaces in your search argument. Use the CLEAN() and/or the TRIM() function and if that doesn't help, use David McRitchies TrimAll function: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Kind regards, Niek Otten Microsoft MVP - Excel "Gary" wrote in message ... | There are no formulas....they are simply names and i need to lookup the | process they are working for. thats it......and its not that A1 is not | there..it is there. even if i drag it down...it shows NA in all the cells... | | and theres one new thing i just discovered. I am trying to lookup A1...the | name in A1 was copied from an email body.....now if i type the same name in | A1, it gives me the result.....but i can not type it....tahts why i ma using | VLOOKUP. its weird...am seeing it for the first time. | please help | | "Alan" wrote in message | ... | There's nothing wrong with the formula, it must be either in the lookup | range or what is in A1. Are the values you're searching the results of | formulas? If so it may well be a rounding issue, | Regards, | Alan. | "Gary" wrote in message | ... | Hi All.... | | Okay.....I dont know WHY this formula is giving me #N/A. | | =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE) | | I do not see anything wrong with this formula....what could cause this??? | | Thanks in advance | Gary | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup not working | Excel Worksheet Functions | |||
VLOOKUP example not working... | Excel Worksheet Functions | |||
vlookup working sometimes, sometimes not | Excel Worksheet Functions | |||
Vlookup no working | New Users to Excel | |||
VLOOKUP not working | Excel Worksheet Functions |