Any help you can give on this minor lookup issue would be appreciated.
I have a set of values, for simple example: ABCD-EFGH & ABCD-EFG2
I am using these values to create a SIMPLE 'Hash'...
A = T
B = Y
C = R
D = P
- Hyphen ignored
E = G
F = A
G = I
H = W
1 = X
2 = Z
Using Index/Match I am able to look up the first set of values (ABCD-EFGH) with no problems. I use Left(D4,1) to lookup the first character, and then a series of Mid(D4,2,1) to get the other characters, finally ending with Right(D4,1). This all works fine.
Also, the first 7 characters of the second value (ABCD-EFG), but, even though '2' is included in the lookup table list and array, the formula of Index/Match of Right(D4,1) returns a #N/A.
I have tested for a number ISNumber(Right(D4,1)) and it returns FALSE, which is correct, it is text. If I convert it to a number using NumberValue(Right(D4,1)) then it works.
However, as the numbers are random in some of the characters, putting NumberValue within the formula then works for the numbers, but NOT the text.
This does not make sense to me, why the issue of looking up '2'?
I appreciate the difference between letters & numbers, but as the result of Right(D4,1) is TEXT, I am wondering whether this is an Excel bug?
I can do Index/Match/Match in my sleep, along with VLookup, but this one has me scratching my head.
Can somebody help me find out what is happening, as this does not make sense to me?
I have a set of values, for simple example: ABCD-EFGH & ABCD-EFG2
I am using these values to create a SIMPLE 'Hash'...
A = T
B = Y
C = R
D = P
- Hyphen ignored
E = G
F = A
G = I
H = W
1 = X
2 = Z
Using Index/Match I am able to look up the first set of values (ABCD-EFGH) with no problems. I use Left(D4,1) to lookup the first character, and then a series of Mid(D4,2,1) to get the other characters, finally ending with Right(D4,1). This all works fine.
Also, the first 7 characters of the second value (ABCD-EFG), but, even though '2' is included in the lookup table list and array, the formula of Index/Match of Right(D4,1) returns a #N/A.
I have tested for a number ISNumber(Right(D4,1)) and it returns FALSE, which is correct, it is text. If I convert it to a number using NumberValue(Right(D4,1)) then it works.
However, as the numbers are random in some of the characters, putting NumberValue within the formula then works for the numbers, but NOT the text.
This does not make sense to me, why the issue of looking up '2'?
I appreciate the difference between letters & numbers, but as the result of Right(D4,1) is TEXT, I am wondering whether this is an Excel bug?
I can do Index/Match/Match in my sleep, along with VLookup, but this one has me scratching my head.
Can somebody help me find out what is happening, as this does not make sense to me?