Index Match - Not Working With A Number

nutrastat

Board Regular
Joined
Nov 1, 2008
Messages
55
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
Joined
Dec 8, 2006
Messages
2,300
Office Version
  1. 2010
Try =IFERROR(VLOOKUP(LEFT(A1,1),your_range,col_nr,0),VLOOKUP(LEFT(A1,1)+0,your_range,col_nr,0))
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
@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
Joined
Nov 1, 2008
Messages
55
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
Joined
Nov 1, 2008
Messages
55

ADVERTISEMENT

@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
Joined
May 28, 2005
Messages
50,622
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 1, 2008
Messages
55

ADVERTISEMENT

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.
 

joeu2004

Banned user
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top