Why do some numerical dates pull and format correctly and others as zero (0)?

[ez08mba]

Problem: My formula to lookup (using INDEX / MATCH) to pull in date values from one workbook to another appears to be working correctly, but some dates just pull in and convert to zero and I'm not sure why?

What I'm trying to do: Is save time rather than copy manually all these over.

The formula in right table 'Custom Field (Actual end)": =IF(MATCH([@[Issue key]],MetricsCapture.xlsx!Table1[Issue key],0),INDEX(MetricsCapture.xlsx!Table1[#Data],MATCH([@[Issue key]],MetricsCapture.xlsx!Table1[Issue key],0),78),"")

Pulling data from the table on the left to the table on the right using that formula:

 

[Kevin_Li]

Check if there is more than one 'AMOD-1492' in the table on the left

 

[awoohaw]

try this:

=IF(isnumber(MATCH([@[Issue key]],MetricsCapture.xlsx!Table1[Issue key],0)),INDEX(MetricsCapture.xlsx!Table1[#Data],MATCH([@[Issue key]],MetricsCapture.xlsx!Table1[Issue key],0),78),"")

 

[ez08mba]

try this:

=IF(isnumber(MATCH([@[Issue key]],MetricsCapture.xlsx!Table1[Issue key],0)),INDEX(MetricsCapture.xlsx!Table1[#Data],MATCH([@[Issue key]],MetricsCapture.xlsx!Table1[Issue key],0),78),"")

Sorry for the late reply, very busy, and thanks for this attempt. The result didn't change.

 

[awoohaw]

okay. well, please help me (and the forum) help you by posting a mini worksheet of your data using the xl2bb add in (link below).
it's hard to debug with no idea of the data or it's structure. In worst case please post the data as a table that can be pasted into a worksheet.
Data in images does not paste.

Thanks in advance.

 

[awoohaw]

and what does this calculation result in:

=MATCH([@[Issue key]],MetricsCapture.xlsx!Table1[Issue key],0)

what value do you get when you type this function:

    =COUNTIF(MetricsCapture.xlsx!Table1[Issue key],[@[Issue key]])

(I may have syntax wrong as I'm winging it. If you get an error try to build these two formulas and share the results. )

 

[awoohaw]

And have you tried the formulas in the MetricsCapture.xlsx workbook itself.

 

[ez08mba]

And have you tried the formulas in the MetricsCapture.xlsx workbook itself.

In setting up files to send you, I copied some data that is being read to a new file since I can't provide the original. Then created the Destination file and it seems to be working. Any thoughts on how to maybe correct the data in the original, or maybe I just need to copy it over into a new file and convert to a new table... do you think that would solve that problem?

 

[ez08mba]

Well I tried to copy all the data over and paste it just as "values" and I'm back to receiving the same results of 0 or 1900 dates. I will see if I can send files after removing some data.

 

[awoohaw]

just copy/paste a portion of the table that condains the look up values. just the table:
MetricsCapture.xlsx!Table1

And, i agree with some of the earlier posts, you have something wrong in your data. So, making a new data set will defeat the purpose of debugging.
Did you do what I asked in the workbook that has Table1 in it?