Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP Problem
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
< 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. |
#6
|
|||
|
|||
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" wrote in message ... < 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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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" wrote in message ... < 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP problem | Excel Discussion (Misc queries) | |||
Problem with VLOOKUP and drop-down lists! | Excel Worksheet Functions | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions | |||
VLOOKUP problem | Excel Worksheet Functions | |||
Excel Problem: VLookup | Excel Worksheet Functions |