Index and match ignor the first zero

[rex759]

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.

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

 

[barry houdini]

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]

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

 

[rex759]

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]

Try replacing the original lookup value...

D40

with

D40+0

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

Hope this helps!

 

[rex759]

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]

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]

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