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

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

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

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

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

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

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.

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.

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

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.

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