Same formula but different results in 2 different cells!!

[itchybumba]

OK so i have the following formula in cell BP152:

=IF(ISNUMBER(SEARCH("*Fail*",HLOOKUP($BM$2,SortedData,$DB$152,FALSE))),1,IF(ISNUMBER(SEARCH("*RFI*",HLOOKUP($BM$2,SortedData,$DB$152,FALSE))),1,0))

SortedData is a dynamic name range. DB152 has the row number that the hlookup should look down to. This formula evaluates to 1 as it should. Now i have copied and pasted that forumla to cell BM152 (i copied the formula across by dragging, by copying and pasting the forumla bar, by remove the =sign, copying it as text and pasting it and replacing the equal bar, copying it into word and then into excel.... but no mattter what i try the formula evaluates to 0 when in cell BM152

i dont understand as the formulas are exactly the same. i have even removed the = sign from both and used =BM152=BP152 which evaluates to TRUE so the formulas are definitely 100% the same. I have tried entering as an array formula, made sure that the spreadsheet is set to auto-calculate (but for fun i set it to manual, calculated and set it back to automatic)..... but still they give different results.

Anyone have any ideas as to why the exact same formula in 2 different cells can evaluate differently?? And just to show they are the same the following 2 lines are the formulas copies and pasted from their respective cells:

=IF(ISNUMBER(SEARCH("*Fail*",HLOOKUP($BM$2,SortedData,$DB$152,FALSE))),1,IF(ISNUMBER(SEARCH("*RFI*",HLOOKUP($BM$2,SortedData,$DB$152,FALSE))),1,0)) - excel evaluating this as 1
=IF(ISNUMBER(SEARCH("*Fail*",HLOOKUP($BM$2,SortedData,$DB$152,FALSE))),1,IF(ISNUMBER(SEARCH("*RFI*",HLOOKUP($BM$2,SortedData,$DB$152,FALSE))),1,0)) - excel evaluating this as 0

HELP!!!! I am going grey over this!!!

[itchybumba]

OK so i worked it out, the problem was in the names range where i used this formula

=OFFSET(Sorted_Data!$A$2,0,0,COUNTA(Sorted_Data!A:A)-1,64)

What i didnt realise was that depending on what column you were in the counta function would count a different column, despite me never dragging the named range around.

Changing that formula to =OFFSET(Sorted_Data!$A$2,0,0,COUNTA(Sorted_Data!$A:$A)-1,64) sorted the problem.

Sorry to have wasted anybodies time!