# Index and match ignor the first zero

#### rex759

##### Well-known Member
Hello,
I trying to match numbers from workbook 1 to a Master workbook where as,

Workbook 1 has numbers that are 4 digits (0123, 0234, 0345, 2134,....8999)

and

The Master workbook numbers are 3-4 digits (123, 234, 345, 2134...8999.

What I was wondering if there is some way to add an If statement something to the effect of

"if first digit of the cell=0, then match the last 3 digits only.

Code:
``=INDEX('C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$E\$5:\$E\$266,MATCH(D40,'C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$B\$5:\$B\$266,0),1)``

P.S. The code works fine when both numbers are 4 digits.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Does this work?

=INDEX('C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$E\$5:\$E\$266,MATCH(D40+0&"",'C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$B\$5:\$B\$266,0),1)

Hello,
Thanks for the quick reply. It didn't work, still getting N/A.

fixed?

Just tried:

=INDEX('C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$E\$5:\$E\$266,MATCH(0&+D40,'C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$B\$5:\$B\$266,0),1)

This works on one section of the numbers, going to test it out further.

Try replacing the original lookup value...

D40

with

D40+0

...without appending &"" to the lookup value.

Hope this helps!

Thank you for your reply.

D40+0 didn't work

0+D40 didn't work

0&+D40 worked!

Then I changed the the formula back to the original for numbers that are always 4 digits.

Thank you

Hopefully this time I've got the data in the right places. Try the following...

Select an empty cell

Edit > Copy

Select B5:B266

Edit > Paste Special > Add > Ok

This will coerce these values into numerical values. Then, if you'd like to retain the four digit format for these cells, custom format them as follows...

Select B5:B266

Format > Cells > Number > Custom > Type: 0000 > OK

Now the original formula should work in all cases. Otherwise, you could replace...

MATCH(D40,'C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$B\$5:\$B\$266,0)

with

MATCH(D40,'C:\Documents and Settings\Owner\Desktop\[master.xls]Numerical Data'!\$B\$5:\$B\$266+0,0)

...and confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Thanks for your reply. Sorry so late in responding, was out of the country. Will give that a try.

Replies
5
Views
501
Replies
2
Views
546
Replies
1
Views
2K
Replies
3
Views
8K
Replies
1
Views
553

Threads
1,218,740
Messages
6,144,211
Members
450,530
Latest member
Echidnadsn

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

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