# Same formula but different results in 2 different cells!!

1. ## Same formula but different results in 2 different cells!!

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!!!

2. ## Re: Same formula but different results in 2 different cells!!

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1