Index Match - Not Working With A Number

[nutrastat]

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?

 

[arthurbr]

Try =IFERROR(VLOOKUP(LEFT(A1,1),your_range,col_nr,0),VLOOKUP(LEFT(A1,1)+0,your_range,col_nr,0))

 

[joeu2004]

@nutrastat.... Since you do not provide your formulas, especially the INDEX/MATCH formula, it is very difficult to help you.

But I think the simple solution is to ensure that the "numbers" 1, 2, 3 etc in the lookup column are entered as text.

There are several ways.

The simplest is to enter them with an apostrophe prefix: '1, '2, '3 etc.

Other ways.... Enter them as formulas in form ="1", ="2", ="3". Or format the cells as Text, then "re-enter" the "numbers" by selecting each cell, pressing the f2 key, then press Enter.

 

[nutrastat]

Try =IFERROR(VLOOKUP(LEFT(A1,1),your_range,col_nr,0),VLOOKUP(LEFT(A1,1)+0,your_range,col_nr,0))

Hi arthurbr,
Thanks for you prompt suggestion.
I can do this, but I would love to know why it fails to address the lookup issue in the first place.
Why cannot it not cope with a number (as text) in the lookup?

 

[nutrastat]

@nutrastat.... Since you do not provide your formulas, especially the INDEX/MATCH formula, it is very difficult to help you.

But I think the simple solution is to ensure that the "numbers" 1, 2, 3 etc in the lookup column are entered as text.

There are several ways.

The simplest is to enter them with an apostrophe prefix: '1, '2, '3 etc.

Other ways.... Enter them as formulas in form ="1", ="2", ="3". Or format the cells as Text, then "re-enter" the "numbers" by selecting each cell, pressing the f2 key, then press Enter.

A big THANK YOU, the apostrophe suggestion did it!
I had set the cells as 'text' in the format table, but this did not work.
I did not post the Index/Match as it is a little 'heavy' as I was giving you an idea of the failure (i.e. text verses number). Here is part of the first 2 sections of Index/Match that I was using:
INDEX(Array_NRS_Random_Letters,MATCH(MID(D5,4,1),List_NRS_CAP_And_No,0),MATCH(Date_Month_No,List_H_NRS_Month_No,0)+1)&INDEX(Array_NRS_Random_Letters,MATCH(MID(D5,7,1),List_NRS_CAP_And_No,0),MATCH(Date_Month_No,List_H_NRS_Month_No,0)+1)
The 'Hash' changes each month, which occurs in the second Match.
Thanks again, and I now know about 'apostrophe for text...

 

[Peter_SSs]

I had set the cells as 'text' in the format table, but this did not work.

FYI: Setting the format to Text after the numbers have been entered does not change their nature, they remain numbers.
You have to format the cells as Text and then enter (or re-enter) the values. If you do do it this way then you don't need the leading apostrophe. (I'm not suggesting the apostrophe is not appropriate, just offering an alternative.)

 

[nutrastat]

FYI: Setting the format to Text after the numbers have been entered does not change their nature, they remain numbers.
You have to format the cells as Text and then enter (or re-enter) the values. If you do do it this way then you don't need the leading apostrophe. (I'm not suggesting the apostrophe is not appropriate, just offering an alternative.)

That again I did not know! That is why I had a problem in the past.
Set cells as 'text' then enter details...got it, and thanks for coming back to me with this important information as well.
Thank you!
It all now works.

 

[Peter_SSs]

You're welcome.

 

[joeu2004]

FYI: Setting the format to Text after the numbers have been entered does not change their nature, they remain numbers. You have to format the cells as Text and then enter (or re-enter) the values.

That again I did not know!

Odd, because I did write: ``then "re-enter" the "numbers" by selecting each cell, pressing the f2 key, then press Enter``.

I guess I shoulda explained why.