ExcelBanter - Vlookup and IF

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Vlookup and IF (https://www.excelbanter.com/excel-programming/388863-vlookup-if.html)

I am unable to figure out the formula for and IF factor in Vlookup.

Example I have three delivery service, Good, Faster, Fastest. each
has a time alloted to them.

Faster is delivered in 2 hours and Fastest is delivered in 1 hour.
The problem is Good. How do I get a formula so that when Good is
entered in the delivery type cell, it will choose the the formula
according to the activated time.

i.e. If p/u at 8:am we have until 5pm to deliver, but if the call is
entered after 11:59am we have until 12noon the next day to deliver. I
cannot use different names, it needs to choose the appropriate
formula.

Thanks for your help
JP

Excel stores the time portion as the decimal portion of a double numeric
format, so let's say you have in column B as level of service, in column C
as Date and Time of call, and you have in column D as Delivery Due Date and
Time.

In Cell D5, you would use the following formula:

=IF(B5="Good",IF(C5-INT(C5)<.5,INT(C5)+TIMEVALUE("17:00:00),INT(C5)+1. 5),IF(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5=" Fastest",C5+TIMEVALUE("01:00:00"),"")))

As you can see, this is using nested functions, which you can have up to 7
levels of nested functions within the same cell.

Ronald R. Dodge, Jr.
Master MOUS 2000

wrote in message
oups.com...
I am unable to figure out the formula for and IF factor in Vlookup.

Example I have three delivery service, Good, Faster, Fastest. each
has a time alloted to them.

Faster is delivered in 2 hours and Fastest is delivered in 1 hour.
The problem is Good. How do I get a formula so that when Good is
entered in the delivery type cell, it will choose the the formula
according to the activated time.

i.e. If p/u at 8:am we have until 5pm to deliver, but if the call is
entered after 11:59am we have until 12noon the next day to deliver. I
cannot use different names, it needs to choose the appropriate
formula.

Thanks for your help
JP

On May 7, 12:15 pm, "Ronald Dodge" wrote:
Excel stores the time portion as the decimal portion of a double numeric
format, so let's say you have in column B as level of service, in column C
as Date and Time of call, and you have in column D as Delivery Due Date and
Time.

In Cell D5, you would use the following formula:

=IF(B5="Good",IF(C5-INT(C5)<.5,INT(C5)+TIMEVALUE("17:00:00),INT(C5)+1. .5),IF*(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5 ="Fastest",C5+TIMEVALUE("01:00:0*0"),"")))

As you can see, this is using nested functions, which you can have up to 7
levels of nested functions within the same cell.

Ronald R. Dodge, Jr.
Master MOUS 2000

wrote in message

oups.com...

I am unable to figure out the formula for and IF factor in Vlookup.

Example I have three delivery service, Good, Faster, Fastest. each
has a time alloted to them.

Faster is delivered in 2 hours and Fastest is delivered in 1 hour.
The problem is Good. How do I get a formula so that when Good is
entered in the delivery type cell, it will choose the the formula
according to the activated time.

i.e. If p/u at 8:am we have until 5pm to deliver, but if the call is
entered after 11:59am we have until 12noon the next day to deliver. I
cannot use different names, it needs to choose the appropriate
formula.

Thanks for your help
JP- Hide quoted text -

- Show quoted text -

Thank you, I could have at this forever

2 things:

First, the TIMEVALUE function needed a closing paranthesis

Second, the last argument of the IF THEN ELSE statement needed to be 2
double quotes, not a double quote and a single quote.

On the other hand, I did notice you corrected my typo by adding a double
quote after the time value for the 5pm one.

=IF(B5="Good",IF(C5-INT(C5)<0.5,INT(C5)+TIMEVALUE("17:00:00"),INT(C5)+ 1.5),IF(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5 ="Fastest",C5+TIMEVALUE("01:00:00"),"")))

wrote in message
ups.com...
On May 7, 12:15 pm, "Ronald Dodge" wrote:
Excel stores the time portion as the decimal portion of a double numeric
format, so let's say you have in column B as level of service, in column C
as Date and Time of call, and you have in column D as Delivery Due Date
and
Time.

In Cell D5, you would use the following formula:

=IF(B5="Good",IF(C5-INT(C5)<.5,INT(C5)+TIMEVALUE("17:00:00),INT(C5)+1. 5),IF*(B5="Faster",C5+TIMEVALUE("02:00:00"),IF(B5= "Fastest",C5+TIMEVALUE("01:00:0*0"),"")))

As you can see, this is using nested functions, which you can have up to 7
levels of nested functions within the same cell.

Ronald R. Dodge, Jr.
Master MOUS 2000

wrote in message

oups.com...

I am unable to figure out the formula for and IF factor in Vlookup.

Example I have three delivery service, Good, Faster, Fastest. each
has a time alloted to them.

Faster is delivered in 2 hours and Fastest is delivered in 1 hour.
The problem is Good. How do I get a formula so that when Good is
entered in the delivery type cell, it will choose the the formula
according to the activated time.

i.e. If p/u at 8:am we have until 5pm to deliver, but if the call is
entered after 11:59am we have until 12noon the next day to deliver. I
cannot use different names, it needs to choose the appropriate
formula.

Thanks for your help
JP- Hide quoted text -

- Show quoted text -

I am getting an error in formula, but am unable to find it, I have
notice that you have a few - where they should not be, I have removed
them, and and yet still getting the error.
Here is what I have entered.

=IF(B5="Good",IF(C5-INT(C5)<.
5,INT(C5)+TIMEVALUE("17:00:00"),INT(C5)+1.5),IF(B5 ="Faster",C5+TIMEVALUE("02:00:00"),IF(B5="Fastest" ,C5+TIMEVALUE("01:00:00","')))