Index and match ignor the first zero

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
610
Office Version
  1. 365
Platform
  1. Windows
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)
 
Upvote 0
Hello,
Thanks for the quick reply. It didn't work, still getting N/A.
 
Upvote 0
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.
 
Upvote 0
Try replacing the original lookup value...

D40

with

D40+0

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

Hope this helps!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Thanks for your reply. Sorry so late in responding, was out of the country. Will give that a try.
 
Upvote 0

Forum statistics

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

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
Back
Top