Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
Trying a vlookup formula one set of paroduct numbers has a little green
triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
Formatting as numbers afterwards is not enough.
To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
Tools Options Error Checking Settings
Uncheck the checkbox 'Enable Background Error Checking' or Tools Options Error Checking Rule Uncheck the checkbox 'Evaluates to Error Value' -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Tom" wrote: Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
Didn't change anything
"Gary Brown" wrote: Tools Options Error Checking Settings Uncheck the checkbox 'Enable Background Error Checking' or Tools Options Error Checking Rule Uncheck the checkbox 'Evaluates to Error Value' -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Tom" wrote: Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
Didn't work either
"Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
It should. Check both the search argument and the table entries with ISTEXT
or ISNUMBER and check the length of both wit LEN(). What is your formula and especially the 4th argument? Is the table sorted ascending? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Didn't work either "Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
I have verified both tables with istext and isnumber. I have copied an
re-pasted as values and formatted those columns as number, they both check out as number and length 6. =VLOOKUP(B4,Sheet4!$A$1:$E$2317,3,FALSE) is the formula I am using. B4 is in the sheet I want to add the information from "Sheet 4 into. Sheet 4 is sorted 0-9, and the data I want is in column 3. I found that if I run it thru Access, everything works the way I want it to go. But why not in Excel??? I'm baffled "Niek Otten" wrote: It should. Check both the search argument and the table entries with ISTEXT or ISNUMBER and check the length of both wit LEN(). What is your formula and especially the 4th argument? Is the table sorted ascending? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Didn't work either "Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
Hi Tom.
You state that your data on Sheet 4 is sorted from 0 - 9, yet the last of your VLOOKUP is "FALSE". False means the data sourse is NOT sorted. I think if you change that FALSE to True, or you unsort your data on Sheet 4 you'll see a difference in the result of your formula. Red -- Just a fellow Excel user here to help when I can..... "Tom" wrote: I have verified both tables with istext and isnumber. I have copied an re-pasted as values and formatted those columns as number, they both check out as number and length 6. =VLOOKUP(B4,Sheet4!$A$1:$E$2317,3,FALSE) is the formula I am using. B4 is in the sheet I want to add the information from "Sheet 4 into. Sheet 4 is sorted 0-9, and the data I want is in column 3. I found that if I run it thru Access, everything works the way I want it to go. But why not in Excel??? I'm baffled "Niek Otten" wrote: It should. Check both the search argument and the table entries with ISTEXT or ISNUMBER and check the length of both wit LEN(). What is your formula and especially the 4th argument? Is the table sorted ascending? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Didn't work either "Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
Hi Red
I've tried it both sorted and unsorted. My understanding of the "False" was that I required an exact match in the data "Red" wrote: Hi Tom. You state that your data on Sheet 4 is sorted from 0 - 9, yet the last of your VLOOKUP is "FALSE". False means the data sourse is NOT sorted. I think if you change that FALSE to True, or you unsort your data on Sheet 4 you'll see a difference in the result of your formula. Red -- Just a fellow Excel user here to help when I can..... "Tom" wrote: I have verified both tables with istext and isnumber. I have copied an re-pasted as values and formatted those columns as number, they both check out as number and length 6. =VLOOKUP(B4,Sheet4!$A$1:$E$2317,3,FALSE) is the formula I am using. B4 is in the sheet I want to add the information from "Sheet 4 into. Sheet 4 is sorted 0-9, and the data I want is in column 3. I found that if I run it thru Access, everything works the way I want it to go. But why not in Excel??? I'm baffled "Niek Otten" wrote: It should. Check both the search argument and the table entries with ISTEXT or ISNUMBER and check the length of both wit LEN(). What is your formula and especially the 4th argument? Is the table sorted ascending? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Didn't work either "Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
And if use:
=isnumber(b4) and then look at sheet4 column a and pick out a the cell that you "know" matches, you could use: =isnumber(sheet4!a99) (change a99 to the correct cell address) or =b4=sheet4!a99 I'd try Niek's suggestion once more if you don't see a match. Tom wrote: I have verified both tables with istext and isnumber. I have copied an re-pasted as values and formatted those columns as number, they both check out as number and length 6. =VLOOKUP(B4,Sheet4!$A$1:$E$2317,3,FALSE) is the formula I am using. B4 is in the sheet I want to add the information from "Sheet 4 into. Sheet 4 is sorted 0-9, and the data I want is in column 3. I found that if I run it thru Access, everything works the way I want it to go. But why not in Excel??? I'm baffled "Niek Otten" wrote: It should. Check both the search argument and the table entries with ISTEXT or ISNUMBER and check the length of both wit LEN(). What is your formula and especially the 4th argument? Is the table sorted ascending? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Didn't work either "Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
<they both check out as number
Is that number format or is that the result of =ISNUMBER(Sheet4!$A$1) and ISNUMBER(B4) check? BTW you'll have to check Sheet4!$A$1:Sheet4!$A$2317 Same for the length <Sheet 4 is sorted 0-9 For column A? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... I have verified both tables with istext and isnumber. I have copied an re-pasted as values and formatted those columns as number, they both check out as number and length 6. =VLOOKUP(B4,Sheet4!$A$1:$E$2317,3,FALSE) is the formula I am using. B4 is in the sheet I want to add the information from "Sheet 4 into. Sheet 4 is sorted 0-9, and the data I want is in column 3. I found that if I run it thru Access, everything works the way I want it to go. But why not in Excel??? I'm baffled "Niek Otten" wrote: It should. Check both the search argument and the table entries with ISTEXT or ISNUMBER and check the length of both wit LEN(). What is your formula and especially the 4th argument? Is the table sorted ascending? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Didn't work either "Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
This is after I format as "Number" prior to that one is number one is test.
What I'd like to do is find a common ground so this formula will work "Niek Otten" wrote: <they both check out as number Is that number format or is that the result of =ISNUMBER(Sheet4!$A$1) and ISNUMBER(B4) check? BTW you'll have to check Sheet4!$A$1:Sheet4!$A$2317 Same for the length <Sheet 4 is sorted 0-9 For column A? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... I have verified both tables with istext and isnumber. I have copied an re-pasted as values and formatted those columns as number, they both check out as number and length 6. =VLOOKUP(B4,Sheet4!$A$1:$E$2317,3,FALSE) is the formula I am using. B4 is in the sheet I want to add the information from "Sheet 4 into. Sheet 4 is sorted 0-9, and the data I want is in column 3. I found that if I run it thru Access, everything works the way I want it to go. But why not in Excel??? I'm baffled "Niek Otten" wrote: It should. Check both the search argument and the table entries with ISTEXT or ISNUMBER and check the length of both wit LEN(). What is your formula and especially the 4th argument? Is the table sorted ascending? -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Didn't work either "Niek Otten" wrote: Formatting as numbers afterwards is not enough. To make the text numbers: - format as number - copy an empty cell - select your "numbers" - EditPaste Special, check Add Now they should be numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "Tom" wrote in message ... Trying a vlookup formula one set of paroduct numbers has a little green triganle in the upper left corner of the cell. The formula returns our friend #N/A. I have tried formatting the entires (one is sent as text, the other as general) as text (both) General (both) and as numbers. There ae no trailing zeros or other odd spaces characters or what not. If I copy one number to the other spreadsheet , it works but when you have several hundereds of rows, that's not practical. Any thought on what is problem/fix is here?? Thanx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Discussion (Misc queries) | |||
VLOOKUP problem | Excel Worksheet Functions | |||
VLookUp problem | Excel Worksheet Functions | |||
vlookup problem | Excel Discussion (Misc queries) | |||
VLookup Problem | Excel Discussion (Misc queries) |