< I think because it passes the point in the list it expects to find the
answer

No. With th 4th argument set to FALSE, the list doesn't have to be sorted,
but is read sequentially from beginning to end (if the enry can't be found).
One consequence is that such a search is considerably slower, which you can
notice if you have hundreds or thousands of such VLOOKUPs.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
I always end up with a #N/A! somewhere or other when I try that - I think
because it passes the point in the list it expects to find the answer, and
stops looking, rather than continuing to the other values. That said, I'm
typically matching people by National Insurance number, or similar, so you
want exact results every time!

Tom.

"Niek Otten" wrote:

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.