The data doesn't have to be sorted if the fourth argument of the function
call is set to FALSE. It defaults to TRUE, which means that you'll get a
result anyway, even if the data looked for isn't there.
It depends on your requirements what is the "right" solution.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
For VLOOKUP to work, data must be correctly sorted, so on the surface of
it,
there is no easy solution.

That said, the reason for the split on the data order when you do sirt it
is
excel is treating some of the values as numeric, and some ax text (it
considers 1 a number, but 1a to be text) and is sorting numbers first.

So, to solve that, try putting an apostrophie ' before each number (e.g. 1
becomes '1). This is a signal used by excel to treat the number as text.
From then on, sorting the way the VLOOKUP needs should still give you a
list
sorted the way you need.

Hope this helps.

Tom.

"Tosca" wrote:

Hi everyone

I have Excel 2003 and several named ranges in a workbook. Several of the
named ranges have data such as:

1a
1d
2
3c
3e
5
9
12
14d
14e
21
129
130a
130b
130d

with data to the right of this column which is retrieved via VLOOKUP.

I need to retain the data in this order because this list is used as a
drop
down list for data validation. When I set up the VLOOKUP, it generated
some errors so I checked to see if the data were in ascending order and,
needless to say, it reordered this data to:

2
5
9
12
21
129
130a
130b
130d
14d
14e
1a
1d
3c
3e


Is it possible to retain the driving data in the order that I need? The
cell format is "General" and the problem remains when I change this to
"Text". I just wonder if a custom format may allow me to do what I need,
but I haven't got a clue what setting I should make.

Thanks in advance.