Index and match ignor the first zero

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
Hello,
Thanks for the quick reply. It didn't work, still getting N/A.
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705

ADVERTISEMENT

Try replacing the original lookup value...

D40

with

D40+0

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

Hope this helps!
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
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!
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
538
Thanks for your reply. Sorry so late in responding, was out of the country. Will give that a try.
 

Forum statistics

Threads
1,136,990
Messages
5,678,983
Members
419,797
Latest member
ikethegenius

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