formula returning #N/A in one workbook but not another

[cjcass]

Hi,
I have two identical workbooks; in one of them the following formula works but in another it returns the #N/A error and I can't figure out why. I appreciate this is probably a difficult one to answer without seeing the files but they conatin sensitive info so I can't share them. I have scoured the data they are reading from for errors, rogue entries etc. but have drawn a complete blank, everything seems identical!! Any ideas of what I could do to try and solve the problem??
Many thanks.

=AVERAGEIF(INDEX('1'!$A$4:$BC$50000,,MATCH($P$12,'1'!$A$3:$BC$3,0)),$P29,'1'!$HJ$4:$HJ$50000)

 

[cjcass]

please ignore this post - problem found in the data.

 

[jmacleary]

Hi, you are right in that it is difficult to steer you. My suggestions for what its worth are:
1 Firstly take a copy of each workbook. Close and reopen excel and the 2 copied books to see if the discrepancy remains.
2 Copy/paste special the data only from the working one over the n/a one and see if the result changes (it should do!). If it doesn't, copy/paste special the formats from good to bad.
3 You should also try the reverse and see if that causes the good sheet to go bad.
4 If the data copy changes the result, then there is definitely some erroneous data in the na sheet. I would try chopping the ranges down - try 25000 instead of 50000. If that changes, your bad data is in 25001-50000, so try the next range as 25001 to 37500 - if it doesnt change try the other section (37501 to 50000) then repeat as often as needed - this may sound like it will take forever, but after a few 'slices' you should home in on the faulty bit (this method is called a binary chop and gets you to a 1 line range in at most 16 slices).

Good luck!
John