It is not so much of omitting the 4th argument that is why it works, but
rather that its default value is TRUE.

This formula works as well

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,TRUE))

and is better IMO as it explicitly states your intent.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Gaffnr" wrote in message
...
Hi Roger.
All these years ive been using that optional 4th character believing it to
be mandatory. I ves tested your theory and works liek a charm . Much
neater
than nested ifs. Thanks for your help.
--
Rob Gaffney


"Roger Govier" wrote:

Hi Rob

Setup a table on say sheet2 in columns A and B
0 Rob
51 Dennis
999 Roger

=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2))

With a value 57, Dennis would be returned - not Rob.
The Vlookup formula (without the optional 4 th parameter of False) will
return the value which does not exceed the looked-up value.

You can have as many ranges as you wish, just ensure that the first on
starts with 0


--
Regards
Roger Govier

"Gaffnr" wrote in message
...
Update - its ok, I found a fault with formula. However, if anybody can
find
a smarter way to do this, id be grateful.

Im thinking a vlookup with a range?

i.e. if cell A1 = 57
do a vlookup on 57
in a lookup table that says 1-50 = Rob, 51 - 90 = Dennis
and return the value Rob

--
Rob Gaffney


"Gaffnr" wrote:

Hi All,

Im using a nested if statement to age documents based upon the number
of
days old a document is. Ive been using this formula for months and
today
the business has decided to change the age buckets so ive simply added
more
nested ifs to my formula. However, the cell displays the formula and
not
the
result whereas before it used to display the reult. All I have done
is
click
in cell, press F2 and edited formula. Is there a maximum number of
nested
if's?

My formula is below

=IF(K3<31,"0 to 30 Days",IF(K3<61,"31 to 60 Days",IF(K3<91,"61 to 90
Days",IF(K3<121,"91 to 120 Days",IF(K3<151,"121 to 150
Days",IF(K3<366,"151
to 365 Days","365+Days"))))))


If there is a maximum no. of nests, can anubody think of another way
to
do
this?
I Can use a vlookup but the number of days old is potentially
limitless
and
i dont want to create a lookup table that big.
--
Rob Gaffney