Excel Help Needed

[linkazoid]

Thank You,

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

Michael

[LosOjos]

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.

[pcstru]

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.

[LosOjos]

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)

[Arthur]

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.

[LosOjos]

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