Why is LOOKUP result changing - sometimes to wrong answer

PTP86

Board Regular
Joined
Nov 7, 2009
Messages
86
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


637664d1566070369-why-is-lookup-result-changing-sometimes-to-wrong-answer-formula-issue.jpg
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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
 
Upvote 0
but you can post a link to the shared excel file with representative example and expected result using OneDrive, GoogleDrive or any similar
 
Upvote 0
You can simply add borders around your cells, and then copy and paste the data into your post...
 
Upvote 0
Here's data for the example that was screenprinted above

Match Date IndexTeam IndexDateTeam IDYellow CardRed CardWas there a card for that team that dateDid the last match in which they got a card feature a red card
1120/03/201615964961TRUEFALSETRUE-
2109/04/201615964961FALSETRUETRUEFALSE
3120/04/201615964961TRUEFALSETRUETRUE
4109/05/201615964961FALSETRUETRUEFALSE
5109/06/201615964961FALSETRUETRUETRUE
6109/07/201615964961FALSETRUETRUETRUE

<tbody>
</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

Match Date IndexTeam IndexDateTeam IDYellow CardRed CardWas there a card for that team that dateDid the last match in which they got a card feature a red card
1107/02/201315964961TRUEFALSETRUE-
1207/02/2013689786TRUEFALSETRUE-
1307/02/20139700024TRUEFALSETRUE-
1407/02/20137846321TRUETRUETRUE-
2114/06/201515964961TRUEFALSETRUEFALSE
2214/06/2015689786TRUEFALSETRUEFALSE
2314/06/20159700024FALSETRUETRUEFALSE
2414/06/20157846321FALSEFALSEFALSETRUE
3131/01/201815964961FALSEFALSEFALSEFALSE
3231/01/2018689786FALSETRUETRUEFALSE
3331/01/20189700024TRUETRUETRUETRUE
3431/01/20187846321TRUEFALSETRUETRUE

<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>
</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
 
Upvote 0
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
 
Upvote 0
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!
 
Last edited:
Upvote 0
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"
 
Last edited:
Upvote 0
Sorry, I'm still unclear. Can you please clarify? It would help if you could run through a few examples that cover all scenarios.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top