INDEX MATCH not working for some values extracted using MID.

Philippa1975

New Member
Joined
Apr 29, 2016
Messages
6
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,072
Office Version
365
Platform
Windows
Hi. How did you extract 5.005 2?? Its 5.5005 with a space at the end isn't it?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
strangely, i found you formula works for both

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">5005</td><td style=";">cost description</td><td style="text-align: right;;">5.5005 </td><td style=";">RC</td><td style="text-align: right;;"></td><td style=";">200.0075.5005 2 0.1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1414</td><td style=";">cost description</td><td style="text-align: right;;">14.0142</td><td style=";">RC</td><td style="text-align: right;;"></td><td style=";">200.00714.0142 0.1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">5.5005 </td><td style="text-align: right;;">5005</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">14.0142</td><td style="text-align: right;;">1414</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=MID(<font color="Blue">F1,8,7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=MID(<font color="Blue">F2,8,7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$2,MATCH(<font color="Red">B4,$C$1:$C$2,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$2,MATCH(<font color="Red">B5,$C$1:$C$2,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,139
Messages
5,466,904
Members
406,507
Latest member
donwiss

This Week's Hot Topics

Top