Results 1 to 8 of 8

Vlookup TRUE or FALSE

This is a discussion on Vlookup TRUE or FALSE within the Excel Questions forums, part of the Question Forums category; Just a silly question..But I do Vlookups all the time and every time, I am using FALSE to get an ...

  1. #1
    Board Regular ShaunD30's Avatar
    Join Date
    Jun 2008
    Location
    Cleveland, Ohio
    Posts
    172

    Default Vlookup TRUE or FALSE

    Just a silly question..But I do Vlookups all the time and every time, I am using FALSE to get an exact match.

    I have read a lot of posts on this message board dealing with Vlookup solutions, and they are all using exact FALSE.

    I tried playing around with using TRUE ..but I don't think I understand what using "TRUE" actually does..and how it effects the formula.

    Can someone help me understand this rarely used method, and offer an example of when to use TRUE rather than FALSE.

    Shaun
    If Beer is Liquid Bread, Guinness is Liquid Cake

  2. #2
    Board Regular
    Join Date
    Dec 2008
    Posts
    5,695

    Default Re: Vlookup TRUE or FALSE

    True gives an approximate match, so if the value you're looking up is not found in the list it uses the nearest it can find without going over.

    For example
    =VLOOKUP(17,A1:A9,1,TRUE)





    5
    10
    15
    20
    25
    30
    35
    40
    45

    would give the value of 15

    looking up any value higher than the range would return an error, and if your lookup range is not in descending order it can give the wrong result.

    Hope this helps

  3. #3
    Board Regular
    Join Date
    Jun 2007
    Posts
    224

    Default Re: Vlookup TRUE or FALSE

    Using TRUE is the same as omitting FALSE and the highest value, less than that required, will be returned.

    Kelbo

  4. #4
    Board Regular ShaunD30's Avatar
    Join Date
    Jun 2008
    Location
    Cleveland, Ohio
    Posts
    172

    Default Re: Vlookup TRUE or FALSE

    If TRUE gives an approximate match if there is no match..shouldn't it still give an exact match if there is one?
    If Beer is Liquid Bread, Guinness is Liquid Cake

  5. #5
    Board Regular ShaunD30's Avatar
    Join Date
    Jun 2008
    Location
    Cleveland, Ohio
    Posts
    172

    Default Re: Vlookup TRUE or FALSE

    And is it used mainly for numbers..or would it be used in cases where you want to lookup text values that might be almost exact..

    example : Ashlee vs Ashley
    If Beer is Liquid Bread, Guinness is Liquid Cake

  6. #6
    Board Regular ShaunD30's Avatar
    Join Date
    Jun 2008
    Location
    Cleveland, Ohio
    Posts
    172

    Default Re: Vlookup TRUE or FALSE

    AHA! I got it. The reason I was so confused was because I never had my table array in ascending order. As soon as I did that...the light came on and my formula worked perfectly. Now I get it

    Thanks

    Shaun
    If Beer is Liquid Bread, Guinness is Liquid Cake

  7. #7
    Board Regular
    Join Date
    Dec 2008
    Posts
    5,695

    Default Re: Vlookup TRUE or FALSE

    Can be used for text as well, and it is less than or equal, so an exact match would still be found, sorry, didn't explain that part properly before.

    Order is still critical regardless of entries though, if it finds a higher value before an exact match it will still take the value before the higher one that it found.

    Ashlee vs Ashley

    searching for Ashley would find Ashlee, but not the other way around.

  8. #8
    Board Regular
    Join Date
    May 2004
    Posts
    379

    Default Re: Vlookup TRUE or FALSE

    It is quite easy to use VLOOKUP(,,,True) for exact matches, and it is orders of magnitude faster.
    See
    http://www.decisionmodels.com/optspeede.htm

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com