# Index/Match - Number formatting

#### sharshra

##### Board Regular
I have to update a table referring to another table. Let's call them working table & reference table. In the reference table, there are 9 digit numbers stored as text. I have to refer these account # in the working table & get the account name.

In the working table, account # are stored as numbers (6 to 9 digits), but needs to be displayed as 9 digits. I have done custom formatting in the working table to include leading zeroes so that account # is displayed with 9 digits.

When I run Index & Match to get the account name, I'm getting error because account # in text format is compared against account # in number format.

To resolve this, I convert account in the working table to text format. I lose leading zeroes when I do this. I need these leading zeroes. Hence I had to do custom format again to make them 9 digits. With this done on single cell, Index & Match is retrieving the account name from reference table. So, account in working table has undergone different format changes - number (original) --> custom (9 digits) --> text --> custom (9 digits).

When I do the same for all cells under account column, it doesn't work. But it works if changes are done in each cell. There are more than 30 thousand rows & it is not feasible to do it for each cell. Not sure why it doesn't work for range of cells.

Is there any better way to do this? Searched internet but couldn't figure out the problem. I must be doing some silly mistake . Can someone help me please? Basically, I have to compare values in text & number format & then run Index-Match to retrieve the values.

Reference table:

Working table:

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### sharshra

##### Board Regular
A correction - When I change to text format & add the leading zeroes, Index/Match works. Basically, it works when the number is in text format with leading zeroes. When it is changed to number format, Index/Match throws error because leading zeroes are removed. Even when custom format is done to make it 9 digits, leading zeroes are not counted while comparing the account #.

#### Alex Blakenburg

##### Well-known Member
See if this does what you need.

Book1
ABCDE
1Working Tbl Numeric Acct NoReturn valueReference Text Acct NoAccount Name
22345678xyz012345678AB
3002345678xyz
Sheet1
Cell Formulas
RangeFormula
B2B2=INDEX(\$D\$2:\$E\$7,MATCH(TEXT(A2,"000000000"),\$D\$2:\$D\$7,0),2)

Hard coding the 9 0s is probably more efficient but you could also use this:-
Excel Formula:
``=INDEX(\$D\$2:\$E\$7,MATCH(TEXT(A2,REPT("0",9)),\$D\$2:\$D\$7,0),2)``

#### sharshra

##### Board Regular
See if this does what you need.

Book1
ABCDE
1Working Tbl Numeric Acct NoReturn valueReference Text Acct NoAccount Name
22345678xyz012345678AB
3002345678xyz
Sheet1
Cell Formulas
RangeFormula
B2B2=INDEX(\$D\$2:\$E\$7,MATCH(TEXT(A2,"000000000"),\$D\$2:\$D\$7,0),2)

Hard coding the 9 0s is probably more efficient but you could also use this:-
Excel Formula:
``=INDEX(\$D\$2:\$E\$7,MATCH(TEXT(A2,REPT("0",9)),\$D\$2:\$D\$7,0),2)``
Your 2nd suggestion to user Text & Rept functions worked . Thank you very much. There are more than 30 thousand rows & hence hard coding is impossible.

#### Alex Blakenburg

##### Well-known Member
Thanks for letting me know. Glad I could help.

Replies
4
Views
86
Replies
2
Views
68
Replies
0
Views
231
Replies
3
Views
262
Replies
11
Views
291

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,036
Messages
5,767,775
Members
425,431
Latest member
Sayson

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