Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!!!
Hi there,
I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am TRYING to use. It keeps sending back a #VALUE! error and I have absolutely no idea why. I have used all the help I can possible find, and I can't see where its going wrong. I can e-mail my worksheet for someone to have a look at. I'm fairly sure its a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) Thank you in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!!!
Hi,
You probably don't need to email your sheet. Just show us a copy of the formula you're using, and some details of the data table you're trying to look up. Regards - Dave. "forevertrying" wrote: Hi there, I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am TRYING to use. It keeps sending back a #VALUE! error and I have absolutely no idea why. I have used all the help I can possible find, and I can't see where its going wrong. I can e-mail my worksheet for someone to have a look at. I'm fairly sure its a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) Thank you in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Make sure that the sheet containing the lookup vlaue, and the table array are
data sorted in the same way. also, in the lookup formula, bracket the table array with $ to make sure it doesnt move when you copy the lookup formula down the list. "Dave" wrote: Hi, You probably don't need to email your sheet. Just show us a copy of the formula you're using, and some details of the data table you're trying to look up. Regards - Dave. "forevertrying" wrote: Hi there, I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am TRYING to use. It keeps sending back a #VALUE! error and I have absolutely no idea why. I have used all the help I can possible find, and I can't see where its going wrong. I can e-mail my worksheet for someone to have a look at. I'm fairly sure its a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) Thank you in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
The formula is:
=F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) F22 is the total hours (result of a sum totalling all hours in the row) The table it is looking the information up from is Column 1 = Reg Column 2 = Hourly Cost There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are formatted as currency. "Dave" wrote: Hi, You probably don't need to email your sheet. Just show us a copy of the formula you're using, and some details of the data table you're trying to look up. Regards - Dave. "forevertrying" wrote: Hi there, I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am TRYING to use. It keeps sending back a #VALUE! error and I have absolutely no idea why. I have used all the help I can possible find, and I can't see where its going wrong. I can e-mail my worksheet for someone to have a look at. I'm fairly sure its a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) Thank you in advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Sorry,
the col_index_num is actually 2. I had been playing with it and not changed it back. "Dave" wrote: Hi, You probably don't need to email your sheet. Just show us a copy of the formula you're using, and some details of the data table you're trying to look up. Regards - Dave. "forevertrying" wrote: Hi there, I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am TRYING to use. It keeps sending back a #VALUE! error and I have absolutely no idea why. I have used all the help I can possible find, and I can't see where its going wrong. I can e-mail my worksheet for someone to have a look at. I'm fairly sure its a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) Thank you in advance |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Omit the quotes around Reg
-- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | The formula is: | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | F22 is the total hours (result of a sum totalling all hours in the row) | The table it is looking the information up from is | Column 1 = Reg | Column 2 = Hourly Cost | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | formatted as currency. | | | | "Dave" wrote: | | Hi, | You probably don't need to email your sheet. | Just show us a copy of the formula you're using, and some details of the | data table you're trying to look up. | Regards - Dave. | | "forevertrying" wrote: | | Hi there, | | I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am | TRYING to use. | | It keeps sending back a #VALUE! error and I have absolutely no idea why. I | have used all the help I can possible find, and I can't see where its going | wrong. | | I can e-mail my worksheet for someone to have a look at. I'm fairly sure its | a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) | | Thank you in advance | | |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Tried that one, but then it just brings up #NAME?
"Niek Otten" wrote: Omit the quotes around Reg -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | The formula is: | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | F22 is the total hours (result of a sum totalling all hours in the row) | The table it is looking the information up from is | Column 1 = Reg | Column 2 = Hourly Cost | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | formatted as currency. | | | | "Dave" wrote: | | Hi, | You probably don't need to email your sheet. | Just show us a copy of the formula you're using, and some details of the | data table you're trying to look up. | Regards - Dave. | | "forevertrying" wrote: | | Hi there, | | I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am | TRYING to use. | | It keeps sending back a #VALUE! error and I have absolutely no idea why. I | have used all the help I can possible find, and I can't see where its going | wrong. | | I can e-mail my worksheet for someone to have a look at. I'm fairly sure its | a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) | | Thank you in advance | | |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
I thought you had column A named Reg.
Use A1 instead of Reg. -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | Tried that one, but then it just brings up #NAME? | | "Niek Otten" wrote: | | Omit the quotes around Reg | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "forevertrying" wrote in message | ... | | The formula is: | | | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | | | F22 is the total hours (result of a sum totalling all hours in the row) | | The table it is looking the information up from is | | Column 1 = Reg | | Column 2 = Hourly Cost | | | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | | formatted as currency. | | | | | | | | "Dave" wrote: | | | | Hi, | | You probably don't need to email your sheet. | | Just show us a copy of the formula you're using, and some details of the | | data table you're trying to look up. | | Regards - Dave. | | | | "forevertrying" wrote: | | | | Hi there, | | | | I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am | | TRYING to use. | | | | It keeps sending back a #VALUE! error and I have absolutely no idea why. I | | have used all the help I can possible find, and I can't see where its going | | wrong. | | | | I can e-mail my worksheet for someone to have a look at. I'm fairly sure its | | a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) | | | | Thank you in advance | | | | | | | |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
The reason you get a value error is either that VLOOKUP returns a text value
that you multiply with the content in F22 or that the content in F22 is text (could be trailing spaces etc) -- Regards, Peo Sjoblom "forevertrying" wrote in message ... Tried that one, but then it just brings up #NAME? "Niek Otten" wrote: Omit the quotes around Reg -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | The formula is: | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | F22 is the total hours (result of a sum totalling all hours in the row) | The table it is looking the information up from is | Column 1 = Reg | Column 2 = Hourly Cost | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | formatted as currency. | | | | "Dave" wrote: | | Hi, | You probably don't need to email your sheet. | Just show us a copy of the formula you're using, and some details of the | data table you're trying to look up. | Regards - Dave. | | "forevertrying" wrote: | | Hi there, | | I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am | TRYING to use. | | It keeps sending back a #VALUE! error and I have absolutely no idea why. I | have used all the help I can possible find, and I can't see where its going | wrong. | | I can e-mail my worksheet for someone to have a look at. I'm fairly sure its | a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) | | Thank you in advance | | |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
I've got the list on the same worksheet, up above it, as I couldn't get it to
notice the list when I put it anywhere else. I've tried changing it and it now says #N/A Think I've been through just about every error message there is now! I just don't understand how I'm having such difficulty. This should be fairly easy surely? "Niek Otten" wrote: I thought you had column A named Reg. Use A1 instead of Reg. -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | Tried that one, but then it just brings up #NAME? | | "Niek Otten" wrote: | | Omit the quotes around Reg | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "forevertrying" wrote in message | ... | | The formula is: | | | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | | | F22 is the total hours (result of a sum totalling all hours in the row) | | The table it is looking the information up from is | | Column 1 = Reg | | Column 2 = Hourly Cost | | | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | | formatted as currency. | | | | | | | | "Dave" wrote: | | | | Hi, | | You probably don't need to email your sheet. | | Just show us a copy of the formula you're using, and some details of the | | data table you're trying to look up. | | Regards - Dave. | | | | "forevertrying" wrote: | | | | Hi there, | | | | I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am | | TRYING to use. | | | | It keeps sending back a #VALUE! error and I have absolutely no idea why. I | | have used all the help I can possible find, and I can't see where its going | | wrong. | | | | I can e-mail my worksheet for someone to have a look at. I'm fairly sure its | | a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) | | | | Thank you in advance | | | | | | | |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
All cells are formatted as numbers and its still showing #N/A
"Peo Sjoblom" wrote: The reason you get a value error is either that VLOOKUP returns a text value that you multiply with the content in F22 or that the content in F22 is text (could be trailing spaces etc) -- Regards, Peo Sjoblom "forevertrying" wrote in message ... Tried that one, but then it just brings up #NAME? "Niek Otten" wrote: Omit the quotes around Reg -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | The formula is: | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | F22 is the total hours (result of a sum totalling all hours in the row) | The table it is looking the information up from is | Column 1 = Reg | Column 2 = Hourly Cost | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | formatted as currency. | | | | "Dave" wrote: | | Hi, | You probably don't need to email your sheet. | Just show us a copy of the formula you're using, and some details of the | data table you're trying to look up. | Regards - Dave. | | "forevertrying" wrote: | | Hi there, | | I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am | TRYING to use. | | It keeps sending back a #VALUE! error and I have absolutely no idea why. I | have used all the help I can possible find, and I can't see where its going | wrong. | | I can e-mail my worksheet for someone to have a look at. I'm fairly sure its | a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) | | Thank you in advance | | |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Hi,
The first argument in VLOOKUP should be a single value or cell. I assume Reg is a named range? If Reg is the name of more than one cell, you will get errors. If you are getting a Name? error, it means XL doesn't recognize the name Reg. Also, if you only have 2 columns, are they Column B and Column D? Regards - Dave "forevertrying" wrote: The formula is: =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) F22 is the total hours (result of a sum totalling all hours in the row) The table it is looking the information up from is Column 1 = Reg Column 2 = Hourly Cost There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are formatted as currency. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Formatting as number does not change text into a number.
Here's my standard checklist: ================================================== =============== 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 "forevertrying" wrote in message ... | All cells are formatted as numbers and its still showing #N/A | | "Peo Sjoblom" wrote: | | The reason you get a value error is either that VLOOKUP returns a text value | that you multiply with the content | in F22 or that the content in F22 is text (could be trailing spaces etc) | | | -- | | | Regards, | | | Peo Sjoblom | | | "forevertrying" wrote in message | ... | Tried that one, but then it just brings up #NAME? | | "Niek Otten" wrote: | | Omit the quotes around Reg | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "forevertrying" wrote in | message | ... | | The formula is: | | | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | | | F22 is the total hours (result of a sum totalling all hours in the row) | | The table it is looking the information up from is | | Column 1 = Reg | | Column 2 = Hourly Cost | | | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly | Costs are | | formatted as currency. | | | | | | | | "Dave" wrote: | | | | Hi, | | You probably don't need to email your sheet. | | Just show us a copy of the formula you're using, and some details of | the | | data table you're trying to look up. | | Regards - Dave. | | | | "forevertrying" wrote: | | | | Hi there, | | | | I am using VLOOKUP to calculate vehicle usage hours... or should I | say, I am | | TRYING to use. | | | | It keeps sending back a #VALUE! error and I have absolutely no idea | why. I | | have used all the help I can possible find, and I can't see where | its going | | wrong. | | | | I can e-mail my worksheet for someone to have a look at. I'm fairly | sure its | | a simple thing to fix, but, as I can't do it myself, I must be | simpler ;o) | | | | Thank you in advance | | | | | | | | | | |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Hi Dave,
Reg is not a named range as I'm not entirely sure what that means or how to do it. I have tried using cell reference B1 as my list covers column b and c instead of Reg but it still brings back an error message. "Dave" wrote: Hi, The first argument in VLOOKUP should be a single value or cell. I assume Reg is a named range? If Reg is the name of more than one cell, you will get errors. If you are getting a Name? error, it means XL doesn't recognize the name Reg. Also, if you only have 2 columns, are they Column B and Column D? Regards - Dave "forevertrying" wrote: The formula is: =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) F22 is the total hours (result of a sum totalling all hours in the row) The table it is looking the information up from is Column 1 = Reg Column 2 = Hourly Cost There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are formatted as currency. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Format does not matter, I can type a word in Excel, format it as a number
and it still would be text. If you have a table that's for instance imported from Access the numbers very often are text, or if you use numbers from internet and have a trailing invisible character the number would still be text regardless if you format it as number. Do this, find the number from VLOOKUP that returns the error, select that particular cell in the lookup table, press F2, make sure there is no trailing or leading space and press enter -- Regards, Peo Sjoblom "forevertrying" wrote in message ... All cells are formatted as numbers and its still showing #N/A "Peo Sjoblom" wrote: The reason you get a value error is either that VLOOKUP returns a text value that you multiply with the content in F22 or that the content in F22 is text (could be trailing spaces etc) -- Regards, Peo Sjoblom "forevertrying" wrote in message ... Tried that one, but then it just brings up #NAME? "Niek Otten" wrote: Omit the quotes around Reg -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | The formula is: | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | F22 is the total hours (result of a sum totalling all hours in the row) | The table it is looking the information up from is | Column 1 = Reg | Column 2 = Hourly Cost | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | formatted as currency. | | | | "Dave" wrote: | | Hi, | You probably don't need to email your sheet. | Just show us a copy of the formula you're using, and some details of the | data table you're trying to look up. | Regards - Dave. | | "forevertrying" wrote: | | Hi there, | | I am using VLOOKUP to calculate vehicle usage hours... or should I say, I am | TRYING to use. | | It keeps sending back a #VALUE! error and I have absolutely no idea why. I | have used all the help I can possible find, and I can't see where its going | wrong. | | I can e-mail my worksheet for someone to have a look at. I'm fairly sure its | a simple thing to fix, but, as I can't do it myself, I must be simpler ;o) | | Thank you in advance | | |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Maybe you should have a fresh start again.
Read this tutorial about VLOOKUP: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | Hi Dave, | | Reg is not a named range as I'm not entirely sure what that means or how to | do it. | | I have tried using cell reference B1 as my list covers column b and c | instead of Reg but it still brings back an error message. | | | | "Dave" wrote: | | Hi, | The first argument in VLOOKUP should be a single value or cell. | I assume Reg is a named range? If Reg is the name of more than one cell, you | will get errors. If you are getting a Name? error, it means XL doesn't | recognize the name Reg. | Also, if you only have 2 columns, are they Column B and Column D? | Regards - Dave | | | "forevertrying" wrote: | | The formula is: | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | F22 is the total hours (result of a sum totalling all hours in the row) | The table it is looking the information up from is | Column 1 = Reg | Column 2 = Hourly Cost | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | formatted as currency. | |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Hi,
If Reg is not a named range, what is it? If it is your column header, then you can't use it as the LOOKUP value. What are you asking VLOOKUP to look for? What data do you have in Column Reg? Dave "forevertrying" wrote: Hi Dave, Reg is not a named range as I'm not entirely sure what that means or how to do it. I have tried using cell reference B1 as my list covers column b and c instead of Reg but it still brings back an error message. "Dave" wrote: Hi, The first argument in VLOOKUP should be a single value or cell. I assume Reg is a named range? If Reg is the name of more than one cell, you will get errors. If you are getting a Name? error, it means XL doesn't recognize the name Reg. Also, if you only have 2 columns, are they Column B and Column D? Regards - Dave "forevertrying" wrote: The formula is: =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) F22 is the total hours (result of a sum totalling all hours in the row) The table it is looking the information up from is Column 1 = Reg Column 2 = Hourly Cost There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are formatted as currency. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Hi Niek,
I have ahad a quick look at that page and am already confused. I have named the range of cells using offset as it says. The next part is where I'm baffled. It says in the example that the code it is looking for is in A7. I thought it would pick the right code/reg out of a list. If I have to right a formula that says which cell it is in, it kind of defeats the object. Is VLOOKUP even the right thing for me? "Niek Otten" wrote: Maybe you should have a fresh start again. Read this tutorial about VLOOKUP: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otten Microsoft MVP - Excel "forevertrying" wrote in message ... | Hi Dave, | | Reg is not a named range as I'm not entirely sure what that means or how to | do it. | | I have tried using cell reference B1 as my list covers column b and c | instead of Reg but it still brings back an error message. | | | | "Dave" wrote: | | Hi, | The first argument in VLOOKUP should be a single value or cell. | I assume Reg is a named range? If Reg is the name of more than one cell, you | will get errors. If you are getting a Name? error, it means XL doesn't | recognize the name Reg. | Also, if you only have 2 columns, are they Column B and Column D? | Regards - Dave | | | "forevertrying" wrote: | | The formula is: | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | F22 is the total hours (result of a sum totalling all hours in the row) | The table it is looking the information up from is | Column 1 = Reg | Column 2 = Hourly Cost | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly Costs are | formatted as currency. | |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returning a #VALUE! error and I've not idea why! HELP!
Hi Niek,
I am new to excel. How do I run Trimall macro in my worksheet? "Niek Otten" wrote: Formatting as number does not change text into a number. Here's my standard checklist: ================================================== =============== 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 "forevertrying" wrote in message ... | All cells are formatted as numbers and its still showing #N/A | | "Peo Sjoblom" wrote: | | The reason you get a value error is either that VLOOKUP returns a text value | that you multiply with the content | in F22 or that the content in F22 is text (could be trailing spaces etc) | | | -- | | | Regards, | | | Peo Sjoblom | | | "forevertrying" wrote in message | ... | Tried that one, but then it just brings up #NAME? | | "Niek Otten" wrote: | | Omit the quotes around Reg | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "forevertrying" wrote in | message | ... | | The formula is: | | | | =F22*VLOOKUP("Reg",$B$1:$D$16,3,FALSE) | | | | F22 is the total hours (result of a sum totalling all hours in the row) | | The table it is looking the information up from is | | Column 1 = Reg | | Column 2 = Hourly Cost | | | | There are 16 Reg Numbers and therefore 16 hourly costs. The Hourly | Costs are | | formatted as currency. | | | | | | | | "Dave" wrote: | | | | Hi, | | You probably don't need to email your sheet. | | Just show us a copy of the formula you're using, and some details of | the | | data table you're trying to look up. | | Regards - Dave. | | | | "forevertrying" wrote: | | | | Hi there, | | | | I am using VLOOKUP to calculate vehicle usage hours... or should I | say, I am | | TRYING to use. | | | | It keeps sending back a #VALUE! error and I have absolutely no idea | why. I | | have used all the help I can possible find, and I can't see where | its going | | wrong. | | | | I can e-mail my worksheet for someone to have a look at. I'm fairly | sure its | | a simple thing to fix, but, as I can't do it myself, I must be | simpler ;o) | | | | Thank you in advance | | | | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Function returning #N/A error for two entries | Excel Discussion (Misc queries) | |||
vlookup returning a #N/A value | Excel Worksheet Functions | |||
SUMPRODUCT formula returning #VALUE! error | Excel Worksheet Functions | |||
Custom function returning VALUE error | Excel Discussion (Misc queries) | |||
Formula returning #N/A Error---why??? | Excel Worksheet Functions |