Why is LOOKUP result changing - sometimes to wrong answer

[PTP86]

Hi

I'm trying to use the LOOKUP function featuring multiple criteria to return a value from the previous occasion where both criteria are TRUE

Please have a look at the screen prints attached.

On this occassion there is just 1 TeamID running through the spreadsheet. But sometimes there are multiple teams.
So I've put in a LOOKUP function with two criteria, one of them being TeamID

Sometimes the formula gives the right answer, other times it gives the wrong answer


Why is this? It's worrying that the answer changes when the formula and inputs are the same.

Calculation mode is set to Automatic and ErrorChecking doesn't say that there are any circular references.
Another thing I've noticed is that sometimes the answer changes purely as a result of me filtering column(s).


Thanks

 

[PTP86]

There's no point in me posting data again. I've already posted a number of examples of the data.
The point of seeing the attachment is that you can see first hand that the formula is all setup correctly and Excel just isn't doing what's expected for some reason.

 

[Domenic]

If you'd like, you can email me your sample workbook. If so, let me know and I'll send you my email address via Private Message.

 

[MARK858]

See the Excel Bug.xlsx‎ attachment

That spreadsheet was just a fresh file. Therefore it can't be a case of the file being corrupt.

When you first open the file, the yellow cell says TRUE which is wrong. It should say FALSE
If you press enter in that cell, or Ctrl,Alt,Shift,F9.....then it changes to FALSE
But this shouldn't be required when calculation mode is set to automatic

When I open the file it shows TRUE but column D has the numbers stored as text, when I changed them to real numbers it returned FALSE, I then saved, closed and reopened the file and it worked correctly.