I have been at this for a long time and I know I have to be missing something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is working
but not fully, I need a formula to say, If the date is <9/20/2005 then look
up the rate in the column 1, but if the date is greater then 9/20/2005 then
look up the rate in column 2, it is looking up the rate but when the date is
changing it does not change the formula to the correct rate. PLEASE HELP!!!

On Wed, 2 Nov 2005 17:03:04 -0800, "L@FUTUREALL"
wrote:

I have been at this for a long time and I know I have to be missing something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is working
but not fully, I need a formula to say, If the date is <9/20/2005 then look
up the rate in the column 1, but if the date is greater then 9/20/2005 then
look up the rate in column 2, it is looking up the rate but when the date is
changing it does not change the formula to the correct rate. PLEASE HELP!!!

Try something like:

=VLOOKUP(lookup_value,table_array,(dtDATE(2005,9, 20))+1,range_lookup)

Note that you don't specify what you wish to happen if the date is equal to
9/20/2005. You may need to modify the formula slightly to account for this --
in the example I give, it will look in column 1.

Look in HELP for the definition of the other terms.


--ron

THANK YOU SO MUCH FOR ANSWERING ME BUT IT DID NOT WORK I THOUGHT THAT I WOULD
NEED TO START OUT USING AN IF FUNCTION AND NEST A V LOOKUP WITHIN THAT?

"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 17:03:04 -0800, "L@FUTUREALL"
wrote:

I have been at this for a long time and I know I have to be missing something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is working
but not fully, I need a formula to say, If the date is <9/20/2005 then look
up the rate in the column 1, but if the date is greater then 9/20/2005 then
look up the rate in column 2, it is looking up the rate but when the date is
changing it does not change the formula to the correct rate. PLEASE HELP!!!

Try something like:

=VLOOKUP(lookup_value,table_array,(dtDATE(2005,9, 20))+1,range_lookup)

Note that you don't specify what you wish to happen if the date is equal to
9/20/2005. You may need to modify the formula slightly to account for this --
in the example I give, it will look in column 1.

Look in HELP for the definition of the other terms.


--ron

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!



"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 17:03:04 -0800, "L@FUTUREALL"
wrote:

I have been at this for a long time and I know I have to be missing something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is working
but not fully, I need a formula to say, If the date is <9/20/2005 then look
up the rate in the column 1, but if the date is greater then 9/20/2005 then
look up the rate in column 2, it is looking up the rate but when the date is
changing it does not change the formula to the correct rate. PLEASE HELP!!!

Try something like:

=VLOOKUP(lookup_value,table_array,(dtDATE(2005,9, 20))+1,range_lookup)

Note that you don't specify what you wish to happen if the date is equal to
9/20/2005. You may need to modify the formula slightly to account for this --
in the example I give, it will look in column 1.

Look in HELP for the definition of the other terms.


--ron

On Wed, 2 Nov 2005 18:50:01 -0800, "L@FUTUREALL"
wrote:

THANK YOU SO MUCH FOR ANSWERING ME BUT IT DID NOT WORK I THOUGHT THAT I WOULD
NEED TO START OUT USING AN IF FUNCTION AND NEST A V LOOKUP WITHIN THAT?

If it's not working there is something else wrong with your formula, or with
your implementation of my suggestion.

(dtDATE(2005,9,20))+1 will return a 1 if date is <=9/20/2005 and a 2 if dt is
9/2/2005. It is in the section of the formula that pertains to column number.

That is what I understood you to want -- change the column number depending on
the relation of date to 20 Sep 2005. Did I misunderstand that specification?

There are certainly many different ways of constructing a formula to obtain the
same result. And it could be done with IF functions. However, since you chose
to not post your formula or your data, or how you integrated my suggestion into
your formula, or what the results were of that formula, it is not possible for
me to troubleshoot.


--ron

Please turn off caps lock!

Biff

"L@FUTUREALL" wrote in message
...
THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS,
IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS
SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!



"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 17:03:04 -0800, "L@FUTUREALL"
wrote:

I have been at this for a long time and I know I have to be missing
something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is
working
but not fully, I need a formula to say, If the date is <9/20/2005 then
look
up the rate in the column 1, but if the date is greater then 9/20/2005
then
look up the rate in column 2, it is looking up the rate but when the
date is
changing it does not change the formula to the correct rate. PLEASE
HELP!!!

Try something like:

=VLOOKUP(lookup_value,table_array,(dtDATE(2005,9, 20))+1,range_lookup)

Note that you don't specify what you wish to happen if the date is equal
to
9/20/2005. You may need to modify the formula slightly to account for
this --
in the example I give, it will look in column 1.

Look in HELP for the definition of the other terms.


--ron

All caps means you are shouting.

Try:
=IF(G4<DATEVALUE("9/18/2005"),VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

9/18/2005 is interpreted by Excel as 9 divided by 18 divided by 2005. Read
Excel help for more info on dates and how Excel stores date values.

"L@FUTUREALL" wrote:

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!



"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 17:03:04 -0800, "L@FUTUREALL"
wrote:

I have been at this for a long time and I know I have to be missing something
small, hopefully small, this is what I am trying to do for a work
spreadsheet when costing some jobs, I need to use vlookup which is working
but not fully, I need a formula to say, If the date is <9/20/2005 then look
up the rate in the column 1, but if the date is greater then 9/20/2005 then
look up the rate in column 2, it is looking up the rate but when the date is
changing it does not change the formula to the correct rate. PLEASE HELP!!!

Try something like:

=VLOOKUP(lookup_value,table_array,(dtDATE(2005,9, 20))+1,range_lookup)

Note that you don't specify what you wish to happen if the date is equal to
9/20/2005. You may need to modify the formula slightly to account for this --
in the example I give, it will look in column 1.

Look in HELP for the definition of the other terms.


--ron

On Wed, 2 Nov 2005 19:06:01 -0800, "L@FUTUREALL"
wrote:

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!



Your IF statement is not working because 9/18/2005 is not being interpreted as
a date; but rather as =9/18/2005 -- 0.000249377. Since the contents of G4
will always be greater than that number, the IF will always evaluate to TRUE.
To use dates in an unambiguous method, either use the DATE function as I did in
my suggestion, or enter the date in some cell and reference that cell.

I would still suggest:

=VLOOKUP(F4,RATE,(G4=DATE(2005,9,18))+2)

as being simpler. Since it is now apparent that you want either column 2 or
column 3, I am adding two instead of 1 to the equality in the "column_number"
argument so as to obtain the correct numbers.

If you really, really want to use your IF statement, then try:

=IF(G4<DATE(2005,9,18),VLOOKUP(F4,RATE,2),VLOOKUP( F4,RATE,3))





--ron

On Wed, 2 Nov 2005 22:06:14 -0800, JMB wrote:

All caps means you are shouting.

Try:
=IF(G4<DATEVALUE("9/18/2005"),VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

9/18/2005 is interpreted by Excel as 9 divided by 18 divided by 2005. Read
Excel help for more info on dates and how Excel stores date values.

Due to international date conventions, I prefer to recommend unambiguous
solutions. So I would suggest either a reference to a cell containing the date
9/18/2005; or the DATE worksheet function; or even DATEVALUE("18 SEP 2005").

Your solution will work fine with US style dates (MDY) set as the option in the
Windows regional settings (Control Panel), but won't with other settings (e.g.
DMY).

--ron

Thank-You, Sorry about the all caps, it is a habit, we use them at work all
of the time, I will try your suggestions out, I am sure they will work.

Thanks,

L@Futureall

"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 19:06:01 -0800, "L@FUTUREALL"
wrote:

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!



Your IF statement is not working because 9/18/2005 is not being interpreted as
a date; but rather as =9/18/2005 -- 0.000249377. Since the contents of G4
will always be greater than that number, the IF will always evaluate to TRUE.
To use dates in an unambiguous method, either use the DATE function as I did in
my suggestion, or enter the date in some cell and reference that cell.

I would still suggest:

=VLOOKUP(F4,RATE,(G4=DATE(2005,9,18))+2)

as being simpler. Since it is now apparent that you want either column 2 or
column 3, I am adding two instead of 1 to the equality in the "column_number"
argument so as to obtain the correct numbers.

If you really, really want to use your IF statement, then try:

=IF(G4<DATE(2005,9,18),VLOOKUP(F4,RATE,2),VLOOKUP( F4,RATE,3))





--ron

This If Statement worked, Thank-You So Much!! I appreciate it!!!!!!

L@Future-all

"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 19:06:01 -0800, "L@FUTUREALL"
wrote:

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!



Your IF statement is not working because 9/18/2005 is not being interpreted as
a date; but rather as =9/18/2005 -- 0.000249377. Since the contents of G4
will always be greater than that number, the IF will always evaluate to TRUE.
To use dates in an unambiguous method, either use the DATE function as I did in
my suggestion, or enter the date in some cell and reference that cell.

I would still suggest:

=VLOOKUP(F4,RATE,(G4=DATE(2005,9,18))+2)

as being simpler. Since it is now apparent that you want either column 2 or
column 3, I am adding two instead of 1 to the equality in the "column_number"
argument so as to obtain the correct numbers.

If you really, really want to use your IF statement, then try:

=IF(G4<DATE(2005,9,18),VLOOKUP(F4,RATE,2),VLOOKUP( F4,RATE,3))





--ron

You're welcome. Glad to help



On Thu, 3 Nov 2005 06:45:09 -0800, "L@FUTUREALL"
wrote:

This If Statement worked, Thank-You So Much!! I appreciate it!!!!!!

L@Future-all

"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 19:06:01 -0800, "L@FUTUREALL"
wrote:

THIS IS THE FORMULA I USED AND IS WORKING TO A POINT

=IF(G4<9/18/2005,VLOOKUP(F4,RATE,2),VLOOKUP(F4,RATE,3))

G4 IS THE DATE, THE COLUMN THE DATE IS IN

F4 IS THE EMPLOYEE CLOCK #, ITS LOOKING UP FOR THE RATE INFO

RATE IS THE DEFINED AREA THAT I SET

2 IS THE COLUMN IT LOOKS THE RATE UP IN

OK JUST SO YOU GET AN IDEA OF WHAT I AM DOING

SO WHAT IS HAPPENING IS THATTHE IF STATEMENT DETERMINES IF THE ARGUMENT IS
TRUE OR FALSE AND THEN IT SHOULD LOOK UP THE RATE IN THE LOOKUP COLUMNS, IT
DOES THAT, BUT THEN I PASTE MY FORMULA DOWN THRU MY SHEET, AND IT IS SAYING
TRUE FOR A FALSE STATEMENT, IF YOU CAN UNDERSTAND WHAT I AM SAYING, LOL

THANK-YOU FOR ANY HELP ON THIS, IT HAS BEEN DRIVING ME NUTS FOR WEEKS!



Your IF statement is not working because 9/18/2005 is not being interpreted as
a date; but rather as =9/18/2005 -- 0.000249377. Since the contents of G4
will always be greater than that number, the IF will always evaluate to TRUE.
To use dates in an unambiguous method, either use the DATE function as I did in
my suggestion, or enter the date in some cell and reference that cell.

I would still suggest:

=VLOOKUP(F4,RATE,(G4=DATE(2005,9,18))+2)

as being simpler. Since it is now apparent that you want either column 2 or
column 3, I am adding two instead of 1 to the equality in the "column_number"
argument so as to obtain the correct numbers.

If you really, really want to use your IF statement, then try:

=IF(G4<DATE(2005,9,18),VLOOKUP(F4,RATE,2),VLOOKUP( F4,RATE,3))





--ron


--ron