+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
General Chat Thread, Excel Help Needed in General; Thank You, I had to copy all my data into a new spreadsheet without any formatting and it's now working ...
  1. #16
    linkazoid's Avatar
    Join Date
    May 2007
    Location
    London
    Posts
    660
    Thank Post
    125
    Thanked 90 Times in 74 Posts
    Rep Power
    79
    Thank You,

    I had to copy all my data into a new spreadsheet without any formatting and it's now working ok!

    Michael

  2. Thanks to linkazoid from:

    mac_shinobi (15th December 2011)

  3. #17

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by Arthur View Post
    The TRUE/FALSE bit at the end, is to tell Excel whether the list is (or isn't) sorted.
    Sorry but that's wrong... it tells Excel whether you're looking for an exact match or a close match, doesn't matter whether the list is sorted. [EDIT] As @pcstru quite rightly points out, the results you get from a range lookup will vary depending on how the list is sorted. To clarify: the range_lookup boolean itself is nothing to do with whether a list is sorted or not, but your mileage may vary depending on how said list has been sorted. [/EDIT]

    You can try it yourself; make a list of random words but make one of them "rage" for example. Make sure none of them say "range". Now do a VLOOKUP for "range" and watch what happens when you change between true and false - true enables a range lookup and will return "rage" as the closest match, false looks for an exact match and so throws an error.
    Last edited by LosOjos; 15th December 2011 at 04:30 PM.

  4. Thanks to LosOjos from:

    mac_shinobi (15th December 2011)

  5. #18


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    Quote Originally Posted by LosOjos View Post
    Sorry but that's wrong... it tells Excel whether you're looking for an exact match or a close match, doesn't matter whether the list is sorted.
    If you are looking for an approximate match, then you may get unexpected results if the list isn't sorted into ascending order. So it does matter.

  6. #19

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by pcstru View Post
    If you are looking for an approximate match, then you may get unexpected results if the list isn't sorted into ascending order. So it does matter.
    Fair point, what I meant though was that the option was nothing to do with whether or not the list is sorted as was suggested (although yes, you're right, results will vary depending on whether it is sorted or not)

  7. #20


    Join Date
    Feb 2007
    Location
    51.403651, -0.515458
    Posts
    9,808
    Thank Post
    262
    Thanked 2,969 Times in 2,183 Posts
    Rep Power
    847
    Quote Originally Posted by LosOjos View Post
    Sorry but that's wrong... it tells Excel whether you're looking for an exact match or a close match
    Well spotted! It's strange that Mr Chandoo (a Microsoft Excel MVP) would make a mistake like this.

  8. #21

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by Arthur View Post
    Well spotted! It's strange that Mr Chandoo (a Microsoft Excel MVP) would make a mistake like this.
    Yeah it wasn't a pop at you just had to point it out in case anybody reading is sat there working themselves in to a fury because it wasn't working as expected



SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

Similar Threads

  1. more excel help needed please
    By RabbieBurns in forum Office Software
    Replies: 10
    Last Post: 7th September 2008, 12:53 AM
  2. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 11:27 AM
  3. Help! Need a BIOS re-flashed
    By ajbritton in forum Hardware
    Replies: 2
    Last Post: 28th December 2006, 01:16 PM
  4. Help needed to reconfig my DC's
    By tosca925 in forum Windows
    Replies: 16
    Last Post: 20th November 2006, 11:45 PM
  5. Help needed creating a DMZ
    By pooley in forum Wireless Networks
    Replies: 12
    Last Post: 11th January 2006, 11:42 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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