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"
wrote in message
news:edwardpestian.29xqdn_1151200503.0558@excelfor um-nospam.com...

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=555301