Index and Match results vary in Excel 2003 and 2007

K

Krish

I have 2 Pivot Tables in a worksheet. They both have Date, Total amount. One Pivot table is producing deposit info from the ERP data while the other one is extracted from Bank ACH payments. Since the transactions are not occurring on the same date, I am trying to match the deposit amount.
My formula is =IF(COUNTIF(F24,"*Total")," ",INDEX(GLCC,MATCH(H24,GLCC,0),0)). GLCC is a named range. Since Pivot table produce Totals by categories, I use COUNTIF. This formula if refreshed in Excel 2003, I get the best results. But if I refresh in Excel 2007 (compatability mode), it shows #N/A. I had verified the correct range in Name in Excel 2007 and ther refer to the correct range.
Could I get any advise from the experts to get the correct results?

DEP_DATE Total Date Total
10/5/2009 4,525.68 4,525.68 10/1/2009 4,418.83 #N/A
10/6/2009 3,583.50 3,583.50 10/8/2009 3,653.69 3653.69
10/7/2009 3,024.79 3,024.79 10/15/2009 6,030.40 6030.4
10/8/2009 6,370.78 6,370.78 10/22/2009 3,968.91 3968.91
10/9/2009 4,991.50 4,991.50 18,071.83
10/12/2009 4,394.37 4,394.37 10/5/2009 5,482.06 #N/A
10/13/2009 2,980.85 #N/A 10/6/2009 4,920.74 #N/A
10/14/2009 1,761.67 1,761.67 10/7/2009 923.14 923.14
10/15/2009 4,574.42 4,574.42 10/9/2009 827.61 827.61
10/16/2009 5,242.27 5,242.27 10/12/2009 515.43 515.43
10/19/2009 4,777.96 4,777.96 10/13/2009 299.22 299.22
10/20/2009 4,318.45 4,318.45 10/14/2009 4,024.47 4024.47
10/21/2009 4,540.09 4,540.09 10/15/2009 582.32 582.32
10/22/2009 3,310.31 #N/A 10/16/2009 3,895.15 #N/A
10/23/2009 1,093.55 #N/A 10/19/2009 208.78 208.78
10/23/2009 6,169.83 #N/A 10/20/2009 2,318.89 2318.89
10/26/2009 7,428.85 #N/A 10/22/2009 467.85 467.85
10/27/2009 3,476.37 #N/A 10/23/2009 1,472.90 #N/A
76,565.24 25,938.56
10/5/2009 3,277.15 3,277.15 10/1/2009 12,563.26 #N/A
10/5/2009 4,094.78 4,094.78 10/2/2009 3,704.96 #N/A
10/7/2009 22,376.79 22,376.79 10/5/2009 5,052.18 #N/A
10/9/2009 3,653.69 3,653.69 10/6/2009 4,525.68 4525.68
10/9/2009 827.61 827.61 10/7/2009 3,583.50 3583.5
10/13/2009 515.43 515.43 10/8/2009 3,024.79 3024.79
10/13/2009 3,652.77 3,652.77 10/9/2009 6,370.78 6370.78
10/14/2009 4,024.47 4,024.47 10/10/2009 499.51 499.51
10/14/2009 299.22 299.22 10/11/2009 427.73 427.73
10/14/2009 582.32 582.32 10/12/2009 4,991.50 4991.5
10/15/2009 6,030.40 6,030.40 10/13/2009 4,394.37 4394.37
10/15/2009 331.78 331.78 10/14/2009 2,980.85 #N/A
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top