Overcome LOOKUP limits and NOW()

[edwardpestian]

I have several new formulas that I've been working on. One
oversimplified example being:

=LOOKUP("lion",{"bird","cat","dog","lion";"chirp","meow","bark","roar"})

It seems that the first set of LOOKUP values must be in consecutive
(alphabetic) in this examle, in order to return the expected value. Is
there a way to overcome this limitation?

Also, I understand that the MOD function returns the remainder after a
number is divided by a divisor. But exactly how is it that MOD(NOW(),1)
returns only the date?

Thanks.

ep

 

[Biff]

Hi!

For your first question:

Use Vlookup instead:

=VLOOKUP("lion",{"cat","meow";"lion","roar";"dog","bark";"bird","chirp"},2,0)

When using the range_lookup argument of FALSE or 0 the table_array doesn't
need to be sorted. (although it's faster if it is)

For your second question:

how is it that MOD(NOW(),1) returns only the date?

Actually, it returns the TIME portion of NOW( ).

Excel stores dates as integer values from a date offset. That date offset is
1/1/1900. Each day has a value of 1 so 1/1/1900 is serial date 1. Today's
date is 6/24/2006. That is the 38,892nd day since 1/1/1900.

A day has a decimal value of 1 so time is the fractional part of a day.
12:00 PM is half a day so its numeric value is 0.5.

So, NOW( ) might return the FORMATTED value of 6/24/2006 22:34 but the true
underlying value is actually 38892.94066.

When we use the MOD function with a divisor of 1:

=MOD(38892.94066,1) = 0.94066 (or the formatted value of 10:34 PM)

Biff

"edwardpestian" <[email protected]>
wrote in message

 

[edwardpestian]

Great reply Biff. Thanks for the detailed explaination.

ep

 

[edwardpestian]

So what am I doing wrong here?

The first example using LOOKUP works as expected; however the second
example using VLOOKUP does not

="Good"&"
"&LOOKUP(MOD(NOW(),1),{0,0.5,0.75},{"Morning","Afternoon","Evening"})&"
"&"Today Is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")

="Good"&"
"&VLOOKUP(MOD(NOW(),1),{0,"Morning";0.5,"Afternoon";0.75,"Evening"},2,0)&"
"&"Today Is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")

Thanks.

ep

 

[Franz Verga]

So what am I doing wrong here?

The first example using LOOKUP works as expected; however the second
example using VLOOKUP does not

="Good"&"
"&LOOKUP(MOD(NOW(),1),{0,0.5,0.75},{"Morning","Afternoon","Evening"})&"
"&"Today Is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")

="Good"&"
"&VLOOKUP(MOD(NOW(),1),{0,"Morning";0.5,"Afternoon";0.75,"Evening"},2,0)&"
"&"Today Is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")

In this case the last parameter of VLOOKUP should be 1, so try this way:

="Good"&" "
&VLOOKUP(MOD(NOW(),1),{0,"Morning";0.5,"Afternoon";0.75,"Evening"},2,1)&" "
&"Today Is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")



--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[daddylonglegs]

So what am I doing wrong here?

The first example using LOOKUP works as expected; however the second
example using VLOOKUP does not

="Good"&"
"&LOOKUP(MOD(NOW(),1),{0,0.5,0.75},{"Morning","Afternoon","Evening"})&"
"&"Today Is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")

="Good"&"
"&VLOOKUP(MOD(NOW(),1),{0,"Morning";0.5,"Afternoon";0.75,"Evening"},2,0)&"
"&"Today Is"&" "&TEXT(TODAY(),"mmmm dd, yyyy")

Thanks.

ep

VLOOKUP with a 4th argument of 0 will allow an unsorted lookup range
but you can only get an exact match, so this formula will only work
when the time is exactly midnight, noon or 6PM. For this situation you
might as well stick with LOOKUP.

BTW you don't need to use "Good"&" "& etc. - you could use "Good "&...