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
|
|

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.

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
| |
| |
|
|
|
|
|
|

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.

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
|
|

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
news | 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.
|

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.

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
news | 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.
|

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
| |
| |
|
|
|
|
|
|