Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
when using vlookup on a list - I often find that I get N/A as an answer
unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
VLOOKUP is tricky. Copy and paste the formula you're using into a response.
Hard to diagnose without looking at your formula. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Patricia Lynch" wrote: when using vlookup on a list - I often find that I get N/A as an answer unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
VLOOKUP is very picky about the values matching exactly. For example, if the
values are strings and one of them has leading or trailing spaces you won't get a match. Also, if the values are numbers but one of them is formatted as text and the other as a number you won't get a match. For this second scenario you can place the number 1 in an unused cell (that is formatted as a number or general) and then copy/paste special/multiply to the values that you want to change to numbers. This will convert them. Hope this helps. Will "Patricia Lynch" wrote: when using vlookup on a list - I often find that I get N/A as an answer unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
Dave-
here's formula =VLOOKUP(M2,Keys!A3:B24,2) I have checked to see that the values that are looked up and the values in the table list are the same format. There are no leading or trailing spaces in the values. The only way I have been able to get this vlookup to work is to retype the value in the table (M2). I have had this happen many times and this time the table is just too big for me to shrug my shoulders and just start retyping the values! thanks Pat "Dave F" wrote: VLOOKUP is tricky. Copy and paste the formula you're using into a response. Hard to diagnose without looking at your formula. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Patricia Lynch" wrote: when using vlookup on a list - I often find that I get N/A as an answer unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
when using vlookup on a list - I often find that I get N/A as an
answer unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? Sometimes, a cell can have an extra space in a text string. You can't tell by looking. Careful use of the TRIM function can help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
See the other responses. Very likely your original values have excess spaces.
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Patricia Lynch" wrote: Dave- here's formula =VLOOKUP(M2,Keys!A3:B24,2) I have checked to see that the values that are looked up and the values in the table list are the same format. There are no leading or trailing spaces in the values. The only way I have been able to get this vlookup to work is to retype the value in the table (M2). I have had this happen many times and this time the table is just too big for me to shrug my shoulders and just start retyping the values! thanks Pat "Dave F" wrote: VLOOKUP is tricky. Copy and paste the formula you're using into a response. Hard to diagnose without looking at your formula. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Patricia Lynch" wrote: when using vlookup on a list - I often find that I get N/A as an answer unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
I've checked the formats and they match; i've reformatted the same cells-
just to be sure; I've checked for leading and trailing spaces; the only thing that works is to retype the number that is being looked up- "roadkill" wrote: VLOOKUP is very picky about the values matching exactly. For example, if the values are strings and one of them has leading or trailing spaces you won't get a match. Also, if the values are numbers but one of them is formatted as text and the other as a number you won't get a match. For this second scenario you can place the number 1 in an unused cell (that is formatted as a number or general) and then copy/paste special/multiply to the values that you want to change to numbers. This will convert them. Hope this helps. Will "Patricia Lynch" wrote: when using vlookup on a list - I often find that I get N/A as an answer unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
================================================== ===============
Your Numbers don't behave (like numbers) Niek Otten, May 11, 2006 Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc. In short: Your Numbers look like Numbers, but they really are Text. Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text! Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's ISNUMBER() function to check your cells; maybe you solved your problem in the first step! · Format an empty cell as Number. Enter the number 1 in it. EditCopy. Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now · If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM() function to remove them · If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function to remove most of them · If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use David McRitchie's TRIMALL() function to remove them. It can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall ================================================== =============== -- Kind regards, Niek Otten Microsoft MVP - Excel "Patricia Lynch" wrote in message ... | when using vlookup on a list - I often find that I get N/A as an answer | unless I retype the item I am looking up. When I retype the value that I am | looking up, the correct corresponding value is then pulled from the list.... | is there a workaround for this other than to retype every value you have to | look up? | thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
Simply re-formatting the cells does not change the fact that the numbers are
text. You must coerce them to become numbers. Format all to General. Copy an empty cell and EditPaste Special(in place)AddOKEsc. Gord Dibben MS Excel MVP On Tue, 27 Feb 2007 09:45:12 -0800, Patricia Lynch wrote: I've checked the formats and they match; i've reformatted the same cells- just to be sure; I've checked for leading and trailing spaces; the only thing that works is to retype the number that is being looked up- "roadkill" wrote: VLOOKUP is very picky about the values matching exactly. For example, if the values are strings and one of them has leading or trailing spaces you won't get a match. Also, if the values are numbers but one of them is formatted as text and the other as a number you won't get a match. For this second scenario you can place the number 1 in an unused cell (that is formatted as a number or general) and then copy/paste special/multiply to the values that you want to change to numbers. This will convert them. Hope this helps. Will "Patricia Lynch" wrote: when using vlookup on a list - I often find that I get N/A as an answer unless I retype the item I am looking up. When I retype the value that I am looking up, the correct corresponding value is then pulled from the list.... is there a workaround for this other than to retype every value you have to look up? thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
Niek- the cells are formatted "General". There are no leading space or
trailing spaces. One other fix I just found is to move onto the cell, hit F2 then move off of the cell..... this corrects the problem. "Niek Otten" wrote: ================================================== =============== Your Numbers don't behave (like numbers) Niek Otten, May 11, 2006 Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc. In short: Your Numbers look like Numbers, but they really are Text. Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text! Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's ISNUMBER() function to check your cells; maybe you solved your problem in the first step! · Format an empty cell as Number. Enter the number 1 in it. EditCopy. Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now · If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM() function to remove them · If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function to remove most of them · If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use David McRitchie's TRIMALL() function to remove them. It can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall ================================================== =============== -- Kind regards, Niek Otten Microsoft MVP - Excel "Patricia Lynch" wrote in message ... | when using vlookup on a list - I often find that I get N/A as an answer | unless I retype the item I am looking up. When I retype the value that I am | looking up, the correct corresponding value is then pulled from the list.... | is there a workaround for this other than to retype every value you have to | look up? | thanks |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
Hi Patricia,
That's exactly what I wrote. I also wrote the solution(s).Please read the entire text. -- Kind regards, Niek Otten Microsoft MVP - Excel "Patricia Lynch" wrote in message ... | Niek- the cells are formatted "General". There are no leading space or | trailing spaces. One other fix I just found is to move onto the cell, hit F2 | then move off of the cell..... this corrects the problem. | | "Niek Otten" wrote: | | ================================================== =============== | Your Numbers don't behave (like numbers) | Niek Otten, May 11, 2006 | | Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc. | In short: | | Your Numbers look like Numbers, but they really are Text. | Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text! | | Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's | ISNUMBER() function to check your cells; maybe you solved your problem in the first step! | | · Format an empty cell as Number. Enter the number 1 in it. EditCopy. | Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now | · If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number | of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM() | function to remove them | · If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function | to remove most of them | · If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use | David McRitchie's TRIMALL() function to remove them. It can be downloaded he | http://www.mvps.org/dmcritchie/excel/join.htm#trimall | | | | ================================================== =============== | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | "Patricia Lynch" wrote in message | ... | | when using vlookup on a list - I often find that I get N/A as an answer | | unless I retype the item I am looking up. When I retype the value that I am | | looking up, the correct corresponding value is then pulled from the list.... | | is there a workaround for this other than to retype every value you have to | | look up? | | thanks | | | |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup bug
Patricia, try this:
highlight column A of your Keys sheet, click Data | Text-to-columns, then click Finish when the first panel pops up - this usually clears the type of error you describe, rather than pressing F2 on every cell in turn. Hope this helps. Pete On Feb 27, 6:34 pm, Patricia Lynch wrote: Niek- the cells are formatted "General". There are no leading space or trailing spaces. One other fix I just found is to move onto the cell, hit F2 then move off of the cell..... this corrects the problem. "Niek Otten" wrote: ================================================== =============== Your Numbers don't behave (like numbers) Niek Otten, May 11, 2006 Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc. In short: Your Numbers look like Numbers, but they really are Text. Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text! Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's ISNUMBER() function to check your cells; maybe you solved your problem in the first step! · Format an empty cell as Number. Enter the number 1 in it. EditCopy. Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now · If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM() function to remove them · If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function to remove most of them · If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use David McRitchie's TRIMALL() function to remove them. It can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall ================================================== =============== -- Kind regards, Niek Otten Microsoft MVP - Excel "Patricia Lynch" wrote in message ... | when using vlookup on a list - I often find that I get N/A as an answer | unless I retype the item I am looking up. When I retype the value that I am | looking up, the correct corresponding value is then pulled from the list.... | is there a workaround for this other than to retype every value you have to | look up? | thanks- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |