# Excel Help Needed

Show 25 post(s) from this thread on one page
Page 2 of 2 First 12
• 15th December 2011, 02:33 PM
Thank You,

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

Michael
• 15th December 2011, 03:09 PM
LosOjos
Quote:

Originally Posted by Arthur
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.
• 15th December 2011, 03:16 PM
pcstru
Quote:

Originally Posted by LosOjos
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.
• 15th December 2011, 03:25 PM
LosOjos
Quote:

Originally Posted by pcstru
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)
• 15th December 2011, 06:53 PM
Arthur
Quote:

Originally Posted by LosOjos
Sorry but that's wrong... it tells Excel whether you're looking for an exact match or a close match

Well spotted! :o It's strange that Mr Chandoo (a Microsoft Excel MVP) would make a mistake like this.
• 16th December 2011, 09:15 AM
LosOjos
Quote:

Originally Posted by Arthur
Well spotted! :o 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 :)
Show 25 post(s) from this thread on one page
Page 2 of 2 First 12