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

 

[Domenic]

The lookup vector/range must be sorted in ascending order, as per Excel's help file...

• Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

If you need further help, try posting a small sample of the data, along with the expected results.

 

[PTP86]

The lookup vector/range must be sorted in ascending order, as per Excel's help file...

If you need further help, try posting a small sample of the data, along with the expected results.

Hi Domenic

Thanks for getting back to me.

This forum won't allow me to upload a sample. It says "Posting Permissions = You may not post attachments"

I can show you a similar forum where I've posted the question and uploaded an example (Excel bug.xlsx)
https://www.excelforum.com/excel-fo...esult-changing-sometimes-to-wrong-answer.html


Also, here is a website that illustrates what I'm trying to do https://www.contextures.com/excelfunctionlookup.html
See Example 6
Neither the [Store], [Fruit] nor [Fresh] fields are in ascending order
My reading of it was that that the "1/ " converts the lookup vector all into a mixture of #DIV/0 and 1, and the #DIV/0 get ignored and so the lookup vector is effectively sorted already.


Thanks

 

[sandy666]

but you can post a link to the shared excel file with representative example and expected result using OneDrive, GoogleDrive or any similar

 

[Domenic]

You can simply add borders around your cells, and then copy and paste the data into your post...

 

[PTP86]

Here's data for the example that was screenprinted above

<tbody>
[TD="class: xl63"]Match Date Index[/TD]
[TD="class: xl66"]Team Index[/TD]
[TD="class: xl70, width: 82"]Date[/TD]
[TD="class: xl63, width: 82"]Team ID[/TD]
[TD="class: xl63, width: 82"]Yellow Card[/TD]
[TD="class: xl71, width: 82"]Red Card[/TD]
[TD="class: xl68, width: 96"]Was there a card for that team that date[/TD]
[TD="class: xl63, width: 137"]Did the last match in which they got a card feature a red card[/TD]

[TD="class: xl64"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl72"]20/03/2016[/TD]
[TD="class: xl64"]15964961[/TD]
[TD="class: xl64"]TRUE[/TD]
[TD="class: xl73"]FALSE[/TD]
[TD="class: xl69"]TRUE[/TD]
[TD="class: xl65"]-[/TD]

[TD="class: xl64"]2[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl72"]09/04/2016[/TD]
[TD="class: xl64"]15964961[/TD]
[TD="class: xl64"]FALSE[/TD]
[TD="class: xl73"]TRUE[/TD]
[TD="class: xl69"]TRUE[/TD]
[TD="class: xl64"]FALSE[/TD]

[TD="class: xl64"]3[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl72"]20/04/2016[/TD]
[TD="class: xl64"]15964961[/TD]
[TD="class: xl64"]TRUE[/TD]
[TD="class: xl73"]FALSE[/TD]
[TD="class: xl69"]TRUE[/TD]
[TD="class: xl64"]TRUE[/TD]

[TD="class: xl64"]4[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl72"]09/05/2016[/TD]
[TD="class: xl64"]15964961[/TD]
[TD="class: xl64"]FALSE[/TD]
[TD="class: xl73"]TRUE[/TD]
[TD="class: xl69"]TRUE[/TD]
[TD="class: xl64"]FALSE[/TD]

[TD="class: xl64"]5[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl72"]09/06/2016[/TD]
[TD="class: xl64"]15964961[/TD]
[TD="class: xl64"]FALSE[/TD]
[TD="class: xl73"]TRUE[/TD]
[TD="class: xl69"]TRUE[/TD]
[TD="class: xl64"]TRUE[/TD]

[TD="class: xl64"]6[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl72"]09/07/2016[/TD]
[TD="class: xl64"]15964961[/TD]
[TD="class: xl64"]FALSE[/TD]
[TD="class: xl73"]TRUE[/TD]
[TD="class: xl69"]TRUE[/TD]
[TD="class: xl64"]TRUE[/TD]

</tbody>

The penultimate column is a simple OR formula
The last column is the column with the complex formula that is behaving unpredictably



Here is another example of what the data could look like and the formula needs to be able to handle, plus the answers it should be getting

<colgroup><col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" width="82" span="6"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:5010;width:103pt" width="137"> </colgroup><tbody>
[TD="class: xl65, width: 82"]Match Date Index[/TD]
[TD="class: xl68, width: 82"]Team Index[/TD]
[TD="class: xl72, width: 82"]Date[/TD]
[TD="class: xl65, width: 82"]Team ID[/TD]
[TD="class: xl65, width: 82"]Yellow Card[/TD]
[TD="class: xl73, width: 82"]Red Card[/TD]
[TD="class: xl70, width: 96"]Was there a card for that team that date[/TD]
[TD="class: xl65, width: 137"]Did the last match in which they got a card feature a red card[/TD]

[TD="class: xl76"]1[/TD]
[TD="class: xl77"]1[/TD]
[TD="class: xl78"]07/02/2013[/TD]
[TD="class: xl76"]15964961[/TD]
[TD="class: xl76"]TRUE[/TD]
[TD="class: xl79"]FALSE[/TD]
[TD="class: xl80"]TRUE[/TD]
[TD="class: xl67"]-[/TD]

[TD="class: xl81"]1[/TD]
[TD="class: xl82"]2[/TD]
[TD="class: xl83"]07/02/2013[/TD]
[TD="class: xl81"]689786[/TD]
[TD="class: xl81"]TRUE[/TD]
[TD="class: xl84"]FALSE[/TD]
[TD="class: xl85"]TRUE[/TD]
[TD="class: xl67"]-[/TD]

[TD="class: xl86"]1[/TD]
[TD="class: xl87"]3[/TD]
[TD="class: xl88"]07/02/2013[/TD]
[TD="class: xl86"]9700024[/TD]
[TD="class: xl86"]TRUE[/TD]
[TD="class: xl89"]FALSE[/TD]
[TD="class: xl90"]TRUE[/TD]
[TD="class: xl67"]-[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl69"]4[/TD]
[TD="class: xl74"]07/02/2013[/TD]
[TD="class: xl66"]7846321[/TD]
[TD="class: xl66"]TRUE[/TD]
[TD="class: xl75"]TRUE[/TD]
[TD="class: xl71"]TRUE[/TD]
[TD="class: xl67"]-[/TD]

[TD="class: xl76"]2[/TD]
[TD="class: xl77"]1[/TD]
[TD="class: xl78"]14/06/2015[/TD]
[TD="class: xl76"]15964961[/TD]
[TD="class: xl76"]TRUE[/TD]
[TD="class: xl79"]FALSE[/TD]
[TD="class: xl80"]TRUE[/TD]
[TD="class: xl66"]FALSE[/TD]

[TD="class: xl81"]2[/TD]
[TD="class: xl82"]2[/TD]
[TD="class: xl83"]14/06/2015[/TD]
[TD="class: xl81"]689786[/TD]
[TD="class: xl81"]TRUE[/TD]
[TD="class: xl84"]FALSE[/TD]
[TD="class: xl85"]TRUE[/TD]
[TD="class: xl66"]FALSE[/TD]

[TD="class: xl86"]2[/TD]
[TD="class: xl87"]3[/TD]
[TD="class: xl88"]14/06/2015[/TD]
[TD="class: xl86"]9700024[/TD]
[TD="class: xl86"]FALSE[/TD]
[TD="class: xl89"]TRUE[/TD]
[TD="class: xl90"]TRUE[/TD]
[TD="class: xl66"]FALSE[/TD]

[TD="class: xl66"]2[/TD]
[TD="class: xl69"]4[/TD]
[TD="class: xl74"]14/06/2015[/TD]
[TD="class: xl66"]7846321[/TD]
[TD="class: xl66"]FALSE[/TD]
[TD="class: xl75"]FALSE[/TD]
[TD="class: xl71"]FALSE[/TD]
[TD="class: xl66"]TRUE[/TD]

[TD="class: xl76"]3[/TD]
[TD="class: xl77"]1[/TD]
[TD="class: xl78"]31/01/2018[/TD]
[TD="class: xl76"]15964961[/TD]
[TD="class: xl76"]FALSE[/TD]
[TD="class: xl79"]FALSE[/TD]
[TD="class: xl80"]FALSE[/TD]
[TD="class: xl66"]FALSE[/TD]

[TD="class: xl81"]3[/TD]
[TD="class: xl82"]2[/TD]
[TD="class: xl83"]31/01/2018[/TD]
[TD="class: xl81"]689786[/TD]
[TD="class: xl81"]FALSE[/TD]
[TD="class: xl84"]TRUE[/TD]
[TD="class: xl85"]TRUE[/TD]
[TD="class: xl66"]FALSE[/TD]

[TD="class: xl86"]3[/TD]
[TD="class: xl87"]3[/TD]
[TD="class: xl88"]31/01/2018[/TD]
[TD="class: xl86"]9700024[/TD]
[TD="class: xl86"]TRUE[/TD]
[TD="class: xl89"]TRUE[/TD]
[TD="class: xl90"]TRUE[/TD]
[TD="class: xl66"]TRUE[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl69"]4[/TD]
[TD="class: xl74"]31/01/2018[/TD]
[TD="class: xl66"]7846321[/TD]
[TD="class: xl66"]TRUE[/TD]
[TD="class: xl75"]FALSE[/TD]
[TD="class: xl71"]TRUE[/TD]
[TD="class: xl66"]TRUE
[/TD]

</tbody>

e.g. Team 4, Match Day 3
The last game prior to 31/01/2018 in which they got a card was 07/02/2013
That game on 07/02/2013 did feature a red card. So the answer is TRUE


The problem is that sometimes when I run the spreadsheet it gives the right answers. Other times it gives the wrong answer in places despite the inputs being the same.
Plus the answer changes on filtering
Plus if you go to Evaluate Formula it gives a different answer (the correct answer) to the answer displayed on screen.
Plus the wrong answers seem to change back to being correct if you go into the cell and press enter
**Calculation mode is set to Automatic and the error checking doesn't show any circular references

I've setup a similar formula on a fresh spreadsheet and it is still showing this unpredictable behaviour

 

[PTP86]

In the first table above, I've illustrated it with the correct answers
The last column of the 09/05/2016 row is the one that is sometimes showing the wrong answer

 

[Domenic]

It looks like a more accurate description of the desired result is that you want to check whether a team received a red card in any of their previous games, not just the last game. If this is correct, assuming that your data starts at A1, try...

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

=IF(ISNUMBER(MATCH(B2,B$1:B1,0)),ISNUMBER(MATCH(TRUE,IF(B$1:B1=B2,F$1:F1),0)),"-")

Hope this helps!

 

[PTP86]

Thanks for your efforts Domenic but this isn't an accurate description of the desired result.

As the column heading indicates, the desired result is
"Did the last match in which they got a card feature a red card"............not "Have they ever had a red card prior to now".......nor "Did their previous match feature a red card"

 

[Domenic]

Sorry, I'm still unclear. Can you please clarify? It would help if you could run through a few examples that cover all scenarios.