# Index Match - Not Working With A Number

#### nutrastat

##### Board Regular
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?

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### arthurbr

##### Well-known Member
Try =IFERROR(VLOOKUP(LEFT(A1,1),your_range,col_nr,0),VLOOKUP(LEFT(A1,1)+0,your_range,col_nr,0))

#### joeu2004

##### Banned user
@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

##### Board Regular
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

##### Board Regular

@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

##### MrExcel MVP, Moderator
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

##### Board Regular

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.

You're welcome.

#### joeu2004

##### Banned user
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.

Replies
2
Views
55
Replies
7
Views
165
Replies
4
Views
115
Replies
2
Views
73
Replies
4
Views
40

1,141,416
Messages
5,706,310
Members
421,441
Latest member
VapesRub

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back