INDEX MATCH not working for some values extracted using MID.

[Philippa1975]

Hi,
because of the way data outputs from our work system, I need to extract a cost code string, from a long accounting string.

I've got one column in some data where the account strings are held.
they all start with the same 7 characters. The cost code is then from the 8th character, for 7 characters.
I've used =MID(string,8,7) to extract the string.
eg from 200.0075.5005 2 0.1 i've extracted 5.005 2
some cost codes have two numbers before the decimal place but are still 7 characters long
eg from 200.00714.0142 0.1 i've extracted 14.0142

In a second sheet I've got a table of values with details relating to the cost codes, set up like...
5005 "cost description" 5.005 2 RC
1414 "cost description" 14.0142 RC
...in columns A to D. These are system produced codes so I can't change anything.

In my sheet with the MID formula, I've got an INDEX MATCH set up to pull out the value in column A from the 2nd sheet - ie from 5.005 2 i want to return 5005:

=INDEX(column A in 2nd sheet,MATCH(sting extracted using MID formula,column C in 2nd sheet,0))

If the MID extraction has one number before the decimal point, the INDEX/MATCH works just fine. If it has 2, it doesn't.

I've done a LEN formula to make sure they are the same length on both sheets and I've checked that they are all formatted the same - ie are all General. I tried changing them to text, and to numbers but nothing works.

Can anyone help?

Thanks

 

[steve the fish]

Hi. How did you extract 5.005 2?? Its 5.5005 with a space at the end isn't it?

 

[Jonmo1]

The result of the MID function will always be considered a TEXT string.

However, on the other sheet, in column C you have a mixture of TEXT and Numbers
5.005 2 <- this is a TEXT string because it has the space before the 2
14.0142 <- this is a NUMBER, no space.

So Text String "14.0142" resulting from the MID function is NOT a match to the NUMBER 14.0142


Try modifying the MID function
=MID(string,8,7)

Change to

=IFERROR(MID(string,8,7)+0,MID(string,8,7))

 

[AlanY]

strangely, i found you formula works for both

Excel 2012
	A	B	C	D	E	F	G
1	5005	cost description	5.5005	RC		200.0075.5005 2 0.1
2	1414	cost description	14.0142	RC		200.00714.0142 0.1
3
4		5.5005	5005
5		14.0142	1414
Sheet1

Cell Formulas
Range	Formula
C1	=MID(F1,8,7)
C2	=MID(F2,8,7)
C4	=INDEX($A$1:$A$2,MATCH(B4,$C$1:$C$2,0))
C5	=INDEX($A$1:$A$2,MATCH(B5,$C$1:$C$2,0))

 

[Philippa1975]

Thanks Jonmo1. That worked perfectly. Many many thanks.

 

[Jonmo1]

You're welcome.