Index/Match - Number formatting

[sharshra]

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:

 

[sharshra]

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]

See if this does what you need.

Book1
	A	B	C	D	E
1	Working Tbl Numeric Acct No	Return value		Reference Text Acct No	Account Name
2	2345678	xyz		012345678	AB
3				002345678	xyz
Sheet1

Cell Formulas
Range	Formula
B2	B2	=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:-

=INDEX($D$2:$E$7,MATCH(TEXT(A2,REPT("0",9)),$D$2:$D$7,0),2)

 

[sharshra]

See if this does what you need.

Book1
	A	B	C	D	E
1	Working Tbl Numeric Acct No	Return value		Reference Text Acct No	Account Name
2	2345678	xyz		012345678	AB
3				002345678	xyz
Sheet1

Cell Formulas
Range	Formula
B2	B2	=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:-

=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]

Thanks for letting me know. Glad I could help.