Why is LOOKUP result changing - sometimes to wrong answer

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).


Update
I've just created an identical scenario on a fresh spreadsheet on a different computer and even then I'm seeing the same problem.
I've uploaded the spreadsheet so you can see. The cell highlighted yellow is the one giving the wrong result.
Also notice that if you go through Evaluate Formula, it comes up with a different answer (the correct answer) to the answer displayed in the cell.


Thanks

Hi and welcome
although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
So, please update your profile to something more precise then "Earth" ( country will suffice, no need to be more precise).
Thank you for helping us to help you

What exactly is the formula supposed to do?

In your attachment it is returning what you are telling it to return in the formula (the last value in F5:F7 where the ID matches and G5:G7 is TRUE, i.e. F7 (FALSE).

Explain in WORDS what you are wanting it to do.

To be clear: the formula is working perfectly, so it's what you are expecting it to do that is awry, and nowhere have you yet told us that. What result are you expecting in H8 and WHY?

Only for a second as the app loads. Once Excel is fully loaded the result is correct. I don't have to go to the cell and force a refresh.

Have you tried running a repair on Excel?

EDIT: I have just opened your workbook again and this time it didn't put itself right until I placed the cursor in the cell and pressed ENTER. Hmmmm ....

EDIT AGAIN: So I tried again, and this time it put itself right as soon as I clicked on Enable Editing. Curiouser and curiouser ...

No, scratch that. It's very odd, and I can't get it to behave consistently. Mostly it corrects itself before I intervene, but on ONE occasion it didn't. Weird!

I have tried several times more and I can no longer replicate the issue - it resolves itself as soon as I click on Enable Editing. Sorry.

I am not convinced that this has anything at all to do with the function used. It will be interesting to see how you get on.