That's two great ideas in one post, Roger!
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Yes, that does make more sense Niek.
|
| I do Agree with Bob though, that it is better to be explicit and use all of
| the arguments, with whatever is appropriate.
| I will cease my lazy ways (or buy the beer in Seattle<bg>)
|
| --
| Regards
| Roger Govier
|
| | > Hi Roger,
| >
| > In the Dutch version of Excel the 4th argument is called Approximate (as a
| > verb). Then the TRUE and FALSE make more sense.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | > | Hi Bob
| > |
| > | Whilst I agree totally with
| > | > It is not so much of omitting the 4th argument that is why it works,
| > but
| > | > rather that its default value is TRUE.
| > |
| > | I have never found the inclusion of TRUE make's the intention clearer.
| > | Inherently this "appears" backwards to me (simple mind<bg>) as TRUE to
| > me
| > | would seem to imply an Exact match, whereas FALSE is the condition that
| > | requires the match to be Exact
| > |
| > | I agree it is lazy to leave out the 4th parameter, but I prefer to use 1
| > and
| > | 0 rather than TRUE or FALSE.
| > |
| > |
| > | --
| > | Regards
| > | Roger Govier
| > |
| > | | > | > 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)
| > | >
| > | >
| > | >
| > | > | > | >> 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
| > | >>>
| > | >>> | > | >>> > 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
| > | >>>
| > | >
| > | >
| >
| >
