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]

Here's another 3 examples. Each time I am describing the formula and desired answer in the bottom right cell

Example 1:
Prior to 17/08/2019, the last match in which they received either card was 18/03/2015, but that didn't feature a red card. So the answer is FALSE

Date	Team	Yellow	Red	Either Card	Did the last match in which they got a card feature a red card
06/04/2012	123456	FALSE	FALSE	FALSE	-
18/03/2015	123456	TRUE	FALSE	TRUE	-
19/01/2017	123456	FALSE	FALSE	FALSE	FALSE
27/05/2019	123456	FALSE	FALSE	FALSE	FALSE
17/08/2019	123456	FALSE	TRUE	TRUE	FALSE

<tbody>
</tbody>

<tbody>
</tbody>

Example 2:
Prior to 17/08/2019, the last match in which they received either card was 19/01/2017, and that did feature a red card. So the answer is TRUE

Date	Team	Yellow	Red	Either Card	Did the last match in which they got a card feature a red card
06/04/2012	123456	FALSE	FALSE	FALSE	-
18/03/2015	123456	TRUE	FALSE	TRUE	-
19/01/2017	123456	TRUE	TRUE	TRUE	FALSE
27/05/2019	123456	FALSE	FALSE	FALSE	TRUE
17/08/2019	123456	TRUE	FALSE	TRUE	TRUE

<tbody>
</tbody>

<tbody>
</tbody>

Example 3
Prior to 17/08/2019, the last match in which they received either card was 27/05/2019, but that didn't feature a red card. So the answer is FALSE

Date	Team	Yellow	Red	Either Card	Did the last match in which they got a card feature a red card
06/04/2012	123456	FALSE	FALSE	FALSE	-
18/03/2015	123456	TRUE	FALSE	TRUE	-
19/01/2017	123456	TRUE	TRUE	TRUE	FALSE
27/05/2019	123456	TRUE	FALSE	TRUE	TRUE
17/08/2019	123456	TRUE	FALSE	TRUE	FALSE

<tbody>
</tbody>

 

[PTP86]

Again, to keep things simple for the examples, I've just shown what the analysis looks like if it features only 1 team.
However, it does sometimes feature more than one team, hence me wanting Team as part of the lookup criteria

 

[Domenic]

In that case, try the following instead...

H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ISNUMBER(MATCH(B2,B$1:B1,0)),INDEX(F$1:F1,MATCH(2,IF(B$1:B1=B2,IF((E$1:E1=TRUE)+(F$1:F1=TRUE)>0,1)),1)),"-")

Does this help?

 

[PTP86]

Thanks Domenic

Yes that gives the right answer (or at least at the moment it is).

But the questions remain, why was my fairly straightforward Lookup function working sometimes and not others? Why was the value in the cell different to the value in Evaluate Formula?

Even one of the experts on the other Excel forum has seen that Lookup is behaving unpredictably in the sample file I uploaded.

 

[Domenic]

Unless I missed it, I don't think you posted the actual formula you were using. What was your formula?

 

[PTP86]

The formula was on the screen prints on Pg 1.
Here it is again, but with references tweaked to match the data you've been looking at.
Note: I recognise the dangers of using IFERROR, I've just put it in here as a quick fix for making the formula fit that example.

=IFERROR(LOOKUP(2,1/((D$1:D1=D2)*(G$1:G1=TRUE)),F$1:F1),"-")

The formula goes in H2 and doesn't need Ctrl-Shift-Enter pressing

It is using the logic seen in Example 6 of https://www.contextures.com/excelfunctionlookup.html

This time it is giving me the right answers to that formula.
But from what I've witnessed, it seems there's a high risk that on another occasion it will give me the wrong answer and the result will sometimes differ between the cell and Evaluate Formula

 

[Domenic]

Your formula looks fine. I have tested your formula, and it seems to return consistent results. Can you provide the sample data where the formula does not return the desired result?

 

[PTP86]

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

 

[Domenic]

By the way, my formula should be amended to use IFERROR instead...

=IFERROR(INDEX(F$1:F1,MATCH(2,IF(B$1:B1=B2,IF((E$1:E1=TRUE)+(F$1:F1=TRUE)>0,1)),1)),"-")

I initially used ISNUMBER(MATCH(...) to avoid calculating an array formula when there's no previous data. But, actually, it would need to be amended to include more than one criteria, otherwise the formula would return #N/A instead of "-" in certain circumstances. So, if an array formula is needed, might as well use IFERROR.

 

[Domenic]

Actually, I'm not a member of that forum. Can you just copy/paste the data? Then I'll test your formula.