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:

 

[ez08mba]

Ok. So if I eliminate a bunch of columns and only provide about a 1/2 dozen and remove some rows for size reasons, while still using the same table and data as before that didn't work - it works. These issue is I cannot provide all the data due to sensitivity. So, not sure where to go from here except knowing something within the table elsewhere may be causing the issue.

 

[ez08mba]

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?

No I hadn't. No disrespect, I got caught up in the copy paste, but you mean the CountIf and the xl2bb? I can do that because your point about trying to keep to the original data makes sense. I could just remove what cannot be seen.

 

[awoohaw]

columns not needed in the calculation can be hidden, or not pasted.

 

[ez08mba]

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?

awoohaw,

I think I have something you can play with that is still doing the same thing. ! I saved a new file with the original "source" data and removed sensitive columns. I then added a worksheet with the destination table (which would be the table I need to update) and removed sensitive columns. The destination table includes the formulas that you asked me to try (i.e., Match and CountIF).

Problem, I've installed the xl2bb and Microsoft is blocking it despite I have the "Trusted" security settings for macros and add-ins turned off. Any thoughts on how to correct that or provide this to you? I've been to the MS page on setting up and managing Add-ins. No success there.

 

[ez08mba]

That xl2bb Addin has messed up my Excel. It was causing everything to open protected, and Microsoft kept blocking it. So I'm not sure how to post this so you can look at it.

 

[Fluff]

Have a look here XL2BB Icons greyed out

 

[ez08mba]

@awoohaw
Finally, with the help from Fluff, here it is finally. I apologize it has been so difficult to get to this point. I appreciate the patience.

Destination table is retrieving from the Source Table. Both tables are included in the same Workbook (MetricsCapture2.xlsx - a saved copy of the original).

Source table:

MetricsCapture2.xlsx
	B																																																														BL
1	Issue key																																																														Custom field (Actual end)
2	AMOD-1532																																																														45041.43056
3	AMOD-1515																																																														45034.43542
4	AMOD-1492																																																														45026.38194
5	AMOD-1461																																																														45034.42083
6	AMOD-1459																																																														45040.61458
7	AMOD-1437																																																														45009.34167
8	AMOD-1435																																																														45028.57778
9	AMOD-1433																																																														45023.48403
10	AMOD-1422																																																														45014.97639
11	AMOD-1420																																																														45002.43125
Source

Destination Table:

MetricsCapture2.xlsx
	A	B	C	D
1	Issue Key	Original Formula	Match Formula	CountIf Formula
2	AMOD-1515	45034.60248	2	1
3	AMOD-1492	0	3	1
4	AMOD-1462	#N/A	#N/A	0
5	AMOD-1461	0	4	1
6	AMOD-1460	#N/A	#N/A	0
7	AMOD-1459	45022.93893	5	1
8	AMOD-1437	0	6	1
9	AMOD-1435	0	7	1
10	AMOD-1433	45022.63403	8	1
11	AMOD-1422	0	9	1
12	AMOD-1421	#N/A	#N/A	0
13	AMOD-1420	45001.60156	10	1
Destination

Cell Formulas
Range	Formula
B2:B13	B2	=IF(MATCH([@[Issue Key]],Table1[Issue key],0),INDEX(Table1[#Data],MATCH([@[Issue Key]],Table1[Issue key],0),78),"")
C2:C13	C2	=MATCH([@[Issue Key]],Table1[Issue key],0)
D2:D13	D2	=COUNTIF(Table1[Issue key],[@[Issue Key]])

 

[Fluff]

Are you trying to get the data from col BL?

 

[awoohaw]

Thanks for posting the data.
I think xl2bb doesn't like using table/column name references in formulas. Or at least they don't copy over well for me.

But, one thing I do notice is that your SOURCE data goes from A to BL which is 63 columns, yet your formula seeks the 78th column in the index data.

Can you just post 3 columns: A, B, C for the source
A: Issue key,
B: Custom Field (Actual Date),
C: What ever the 78th column is.

# ROWS: only the rows where you have a few good matches and some where you have the bad matches.

Then tell us what you want to return in the formula in the destination worksheet.
ROWS: Some ISSUE KEY numbers that are in the list in the first part and some not in the list.

It looks like you want to lookup column B when you want to have a match with an Issue Key (column A).
But, what is the purpose of the other 2 columns in your destination? (Or was it you trying to debug your formula?)

But, again, if your INDEX(Match) formula is actually resolving to a value of 0, it is probably because the lookup cell has a blank in it.

 

[ez08mba]

Thanks for posting the data.
I think xl2bb doesn't like using table/column name references in formulas. Or at least they don't copy over well for me.

But, one thing I do notice is that your SOURCE data goes from A to BL which is 63 columns, yet your formula seeks the 78th column in the index data.

Can you just post 3 columns: A, B, C for the source
A: Issue key,
B: Custom Field (Actual Date),
C: What ever the 78th column is.

# ROWS: only the rows where you have a few good matches and some where you have the bad matches.

Then tell us what you want to return in the formula in the destination worksheet.
ROWS: Some ISSUE KEY numbers that are in the list in the first part and some not in the list.

It looks like you want to lookup column B when you want to have a match with an Issue Key (column A).
But, what is the purpose of the other 2 columns in your destination? (Or was it you trying to debug your formula?)

But, again, if your INDEX(Match) formula is actually resolving to a value of 0, it is probably because the lookup cell has a blank in it.

Hi awoohaw!

Great news! That was the issue.... 78 should have been 64 actually. The values in column 78 were a different date value format (27:34.0) but also had blanks which returned 0 as you suspected.

Anyhow, this is why when I would remove columns and rows, and changing 78 to 2, like the examples below, they would return correct values. I just somehow entered a wrong column number that happen to have date values.

I so, so, appreciate your patience with me in getting me to this point and with learning xl2bb (thanks Fluff) so I could post what you needed.

MetricsCapture2.xlsx
	A	B
1	Issue key	Custom field (Actual end)
2	AMOD-1532	45041.43056
3	AMOD-1515	45034.43542
4	AMOD-1492	45026.38194
5	AMOD-1461	45034.42083
6	AMOD-1459	45040.61458
7	AMOD-1437	45009.34167
8	AMOD-1435	45028.57778
9	AMOD-1433	45023.48403
10	AMOD-1422	45014.97639
11	AMOD-1420	45002.43125
12	AMOD-1418	45026.54167
13	AMOD-1415	45005.49792
14	AMOD-1413	45002.43333
15	AMOD-1393	44999.41736
Source

MetricsCapture2.xlsx
	A	B	C
1	Issue Key	Original Formula	Previous Formula Value
2	AMOD-1515	45034.43542
3	AMOD-1492	45026.38194	was 0
4	AMOD-1462	#N/A
5	AMOD-1461	45034.42083	was 0
6	AMOD-1460	#N/A
7	AMOD-1459	45040.61458
8	AMOD-1437	45009.34167	was 0
9	AMOD-1435	45028.57778	was 0
10	AMOD-1433	45023.48403
11	AMOD-1422	45014.97639	was 0
12	AMOD-1421	#N/A
13	AMOD-1420	45002.43125
14	AMOD-1418	45026.54167
Destination

Cell Formulas
Range	Formula
B2:B14	B2	=IF(MATCH([@[Issue Key]],Table1[Issue key],0),INDEX(Table1[#Data],MATCH([@[Issue Key]],Table1[Issue key],0),2),"")