VLOOKUP Problem

Since you are on Excel 2003, there is no need for dynamic named ranges.
Convert all table areas into a LIST by means of Data|List|Create List.
If you can sort them in ascending order and maintain them in ascending
order, do so.

Lets A1:D200 house such a table with headers (fields) in A1:D1...

Turn A1:D200 into a LIST.
Select A2:D10, go to the Name Box on the Formula Bar, type TABLE, and
hit enter.

Any appropriate cell that you want to data validate, invoke:

=INDEX(TABLE,0,1)

in the Source box in order to have the items in A2:A100 as a list in
that cell.

If F2 is a data validated cell, you can invoke in, say, G2 a lookup
formula like...

(a) If TABLE is in ascending order, then:

=VLOOKUP(F2,Table,2,1)

(b) If TABLE is not sorted, then:

=VLOOKUP(F2,Table,2,0)

The one in (a) is quite faster

Tosca wrote:
> Hi Niek
>
> Yes, this works fine. Within each table for the lookup, there will be upto
> 30 rows of data, not several hundred, so I don't think that the speed will
> be an issue. There will, however, be many (perhaps 200+) separate tables as
> named ranges and I doubt that the VLOOKUP will be slow in this case as it is
> looking at a specific named range of upto 30 rows, rather than looking at
> each of the tables in sequence and then down each of the rows to find the
> data. Is this argument logical? If so, I'm happy, otherwise I may have to
> consider some other solution. The data *will* be found by VLOOKUP as I'm
> using the first column for data validation when I'm entering the data in the
> first place.
>
> Thanks again for your time.
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
>
>>< 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
>
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Since you are on Excel 2003, there is no need for dynamic named ranges.
Convert all table areas into a LIST by means of Data|List|Create List.
If you can sort them in ascending order and maintain them in ascending
order, do so.

Lets A1:D200 house such a table with headers (fields) in A1:D1...

Turn A1:D200 into a LIST.
Select A2:D10, go to the Name Box on the Formula Bar, type TABLE, and
hit enter.

Any appropriate cell that you want to data validate, invoke:

=INDEX(TABLE,0,1)

in the Source box in order to have the items in A2:A100 as a list in
that cell.

If F2 is a data validated cell, you can invoke in, say, G2 a lookup
formula like...

(a) If TABLE is in ascending order, then:

=VLOOKUP(F2,Table,2,1)

(b) If TABLE is not sorted, then:

=VLOOKUP(F2,Table,2,0)

The one in (a) is quite faster

Tosca wrote:
> Hi Niek
>
> Yes, this works fine. Within each table for the lookup, there will be upto
> 30 rows of data, not several hundred, so I don't think that the speed will
> be an issue. There will, however, be many (perhaps 200+) separate tables as
> named ranges and I doubt that the VLOOKUP will be slow in this case as it is
> looking at a specific named range of upto 30 rows, rather than looking at
> each of the tables in sequence and then down each of the rows to find the
> data. Is this argument logical? If so, I'm happy, otherwise I may have to
> consider some other solution. The data *will* be found by VLOOKUP as I'm
> using the first column for data validation when I'm entering the data in the
> first place.
>
> Thanks again for your time.
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
>
>>< 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
>
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Since you are on Excel 2003, there is no need for dynamic named ranges.
Convert all table areas into a LIST by means of Data|List|Create List.
If you can sort them in ascending order and maintain them in ascending
order, do so.

Lets A1:D200 house such a table with headers (fields) in A1:D1...

Turn A1:D200 into a LIST.
Select A2:D10, go to the Name Box on the Formula Bar, type TABLE, and
hit enter.

Any appropriate cell that you want to data validate, invoke:

=INDEX(TABLE,0,1)

in the Source box in order to have the items in A2:A100 as a list in
that cell.

If F2 is a data validated cell, you can invoke in, say, G2 a lookup
formula like...

(a) If TABLE is in ascending order, then:

=VLOOKUP(F2,Table,2,1)

(b) If TABLE is not sorted, then:

=VLOOKUP(F2,Table,2,0)

The one in (a) is quite faster

Tosca wrote:
> Hi Niek
>
> Yes, this works fine. Within each table for the lookup, there will be upto
> 30 rows of data, not several hundred, so I don't think that the speed will
> be an issue. There will, however, be many (perhaps 200+) separate tables as
> named ranges and I doubt that the VLOOKUP will be slow in this case as it is
> looking at a specific named range of upto 30 rows, rather than looking at
> each of the tables in sequence and then down each of the rows to find the
> data. Is this argument logical? If so, I'm happy, otherwise I may have to
> consider some other solution. The data *will* be found by VLOOKUP as I'm
> using the first column for data validation when I'm entering the data in the
> first place.
>
> Thanks again for your time.
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
>
>>< 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
>
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Since you are on Excel 2003, there is no need for dynamic named ranges.
Convert all table areas into a LIST by means of Data|List|Create List.
If you can sort them in ascending order and maintain them in ascending
order, do so.

Lets A1:D200 house such a table with headers (fields) in A1:D1...

Turn A1:D200 into a LIST.
Select A2:D10, go to the Name Box on the Formula Bar, type TABLE, and
hit enter.

Any appropriate cell that you want to data validate, invoke:

=INDEX(TABLE,0,1)

in the Source box in order to have the items in A2:A100 as a list in
that cell.

If F2 is a data validated cell, you can invoke in, say, G2 a lookup
formula like...

(a) If TABLE is in ascending order, then:

=VLOOKUP(F2,Table,2,1)

(b) If TABLE is not sorted, then:

=VLOOKUP(F2,Table,2,0)

The one in (a) is quite faster

Tosca wrote:
> Hi Niek
>
> Yes, this works fine. Within each table for the lookup, there will be upto
> 30 rows of data, not several hundred, so I don't think that the speed will
> be an issue. There will, however, be many (perhaps 200+) separate tables as
> named ranges and I doubt that the VLOOKUP will be slow in this case as it is
> looking at a specific named range of upto 30 rows, rather than looking at
> each of the tables in sequence and then down each of the rows to find the
> data. Is this argument logical? If so, I'm happy, otherwise I may have to
> consider some other solution. The data *will* be found by VLOOKUP as I'm
> using the first column for data validation when I'm entering the data in the
> first place.
>
> Thanks again for your time.
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
>
>>< 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
>
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

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.

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.
>
>
>

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
news:[email protected]...
> 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.
>>
>>
>>

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
> news:[email protected]...
> > 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.
> >>
> >>
> >>
>
>
>

< 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
news:[email protected]...
>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
>> news:[email protected]...
>> > 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.
>> >>
>> >>
>> >>
>>
>>
>>

Hi Niek

Yes, this works fine. Within each table for the lookup, there will be upto
30 rows of data, not several hundred, so I don't think that the speed will
be an issue. There will, however, be many (perhaps 200+) separate tables as
named ranges and I doubt that the VLOOKUP will be slow in this case as it is
looking at a specific named range of upto 30 rows, rather than looking at
each of the tables in sequence and then down each of the rows to find the
data. Is this argument logical? If so, I'm happy, otherwise I may have to
consider some other solution. The data *will* be found by VLOOKUP as I'm
using the first column for data validation when I'm entering the data in the
first place.

Thanks again for your time.

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
>< 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

To get the order you want, with your sample data in cells C1 to C15, enter
the following:
In cell D1:
=IF(ISTEXT(C1)=FALSE,C1&"x",C1)
In cell E1:
=RIGHT(D1)
In cell F1:
=LEFT(D1,LEN(D1)-1)
Sort by column F ascending, then by column E ascending. Choose to sort
anything that looks like a number as a number in the Sort Warning. Column C
will give you the sort order.

"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.
>
>
>

Since you are on Excel 2003, there is no need for dynamic named ranges.
Convert all table areas into a LIST by means of Data|List|Create List.
If you can sort them in ascending order and maintain them in ascending
order, do so.

Lets A1:D200 house such a table with headers (fields) in A1:D1...

Turn A1:D200 into a LIST.
Select A2:D10, go to the Name Box on the Formula Bar, type TABLE, and
hit enter.

Any appropriate cell that you want to data validate, invoke:

=INDEX(TABLE,0,1)

in the Source box in order to have the items in A2:A100 as a list in
that cell.

If F2 is a data validated cell, you can invoke in, say, G2 a lookup
formula like...

(a) If TABLE is in ascending order, then:

=VLOOKUP(F2,Table,2,1)

(b) If TABLE is not sorted, then:

=VLOOKUP(F2,Table,2,0)

The one in (a) is quite faster

Tosca wrote:
> Hi Niek
>
> Yes, this works fine. Within each table for the lookup, there will be upto
> 30 rows of data, not several hundred, so I don't think that the speed will
> be an issue. There will, however, be many (perhaps 200+) separate tables as
> named ranges and I doubt that the VLOOKUP will be slow in this case as it is
> looking at a specific named range of upto 30 rows, rather than looking at
> each of the tables in sequence and then down each of the rows to find the
> data. Is this argument logical? If so, I'm happy, otherwise I may have to
> consider some other solution. The data *will* be found by VLOOKUP as I'm
> using the first column for data validation when I'm entering the data in the
> first place.
>
> Thanks again for your time.
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
>
>>< 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
>
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.