Index/Match - Number formatting

sharshra

Board Regular
Joined
Mar 20, 2013
Messages
223
Office Version
  1. 365
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 :confused:. 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:
ref table.PNG


Working table:
working table.PNG
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

sharshra

Board Regular
Joined
Mar 20, 2013
Messages
223
Office Version
  1. 365
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
Joined
Feb 23, 2021
Messages
2,650
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

sharshra

Board Regular
Joined
Mar 20, 2013
Messages
223
Office Version
  1. 365
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 (y). Thank you very much. There are more than 30 thousand rows & hence hard coding is impossible.
 
Master Excel Bundle

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.

Forum statistics

Threads
1,152,032
Messages
5,767,750
Members
425,430
Latest member
corinaas

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