VLOOKUP Problem (and quick Q about dropdowns)

[tannersarms]

Hi Everyone

I'm trying to work on a project for work that relies heavily on Excel, but I'm alittle bit rusty in my excel skills compared to how I could use it at University a few years ago. I have a few questions and after searching around on google I've figured this forum is one of the best places to ask questions.

Firstly, I think I know the answer to this, but am I right in thinking that it is not possible in Excel to combine drop down menus with predictive typing? I know this can be done in Access, but if my memory serves correctly, the only way to do this in Excel is through a series of filters. The list I want to use is some 226 entries long (it's a list of all countries) so I don't fancy fiddling around with multiple filters for each First Letter, then each second letter and so on)

SecondlyI am trying to use a vlookup function that converts Text and Numbers (and the symbol "<" which I need to use as a filler) into all numbers, i.e

0=0
1=1
2=2
..
9=9
A=10
B=11
..
Z=35
<=0

Just to explain, the inputted numbers will never be greater than 9.

I am trying to break down an 9 Digit ID Code that may be alphanumeric and assign each component of that ID Code a numeric equivalent that I can then manipulate.

For example, AS1234x4< , which I've broken up into single digit column so it reads like follows and I am applying the VLOOKUP as pasted
C D
22 A =VLOOKUP(C22,Numbers,2) 10 (Correct)
23 S =VLOOKUP(C23,Numbers,2) 28 (Correct)
24 1 =VLOOKUP(C24,Numbers,2) 0 (INCORRECT)
25 2 0 (INCORRECT)
26 3 0 (INCORRECT)
27 4 0 (INCORRECT)
28 X 33 (Correct)
29 4 0 (INCORRECT)
30 < 0 (Correct, but could be dumb luck, could be logic)

"Numbers" is the name I have given to an array appearing on Worksheet 2, which looks like this:

< 0
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
A 10
B 11
C 12
D 13
E 14
F 15
G 16
H 17
I 18
J 19
K 20
L 21
M 22
N 23
O 24
P 25
Q 26
R 27
S 28
T 29
U 30
V 31
W 32
X 33
Y 34
Z 35



But for some reason, it is only giving the right answer to the Letters, the numbers and < result in an 0. I know that there is a rule in VLOOKUP where the list has to be ascending, but despite trying several ways of listing the data (not sure how numbers, letters and symbols ascend in the right order) I still can only get the wrong answer for numbers.

Can anyone shed some light on why the numbers aren't transferring across as they should when this forumla is applied? The only thing I can think of is the data in column C has been transferred there from a single cell using the MID function - is it possible that it is no longer seen as a number?

Thanks for any help

Mark

 

[acw]

Mark

1) Dropdowns: ASFIK this is correct. No predictive typing.

2) Correct. The mid function will return the number as a text value, so it is not picking it up in the named range. You could either (a) change the numbers in the lookup range to be text by prefixing with a ' and then resort or (b) make the output from the MID function return a number rather than text. Given that you have both alpha and numberic returning, it may be that you will have to look at the ASCII value of the result (CODE) and if it is in the range 48-57 (0 - 9) then multiply it by 1 to convert (or some other conversion) otherwise leave it as text.


HTH

Tony

 

[tannersarms]

Thank you Tony for the quick reply.

I tried your first suggestion (prefacing the numbers in the lookup column with ') and it worked wonders. Just one small problem though... Now my "<"'s are returning a N/A error, regardless of whether I tell the LookUp column that they a text ('<) or just enter it plain (<)

Any suggestions on that little problem?

Thanks again

Mark

EDIT: I resorted the Column and got a message asking whether I wanted everything that looked like a number to be treated as a number, or whether everything should be treated as text. I went with the latter and it has solved the problem. Thanks again.