Why is LOOKUP result changing - sometimes to wrong answer

P

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
 
Sort by date Sort by votes
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


<tbody>
[TD="class: xl65"]Date[/TD]
[TD="class: xl65"]Team[/TD]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]Either Card[/TD]
[TD="class: xl66"]Did the last match in which they got a card feature a red card[/TD]

[TD="class: xl67, align: right"]06/04/2012[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]-[/TD]

[TD="class: xl67, align: right"]18/03/2015[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65"]-[/TD]

[TD="class: xl67, align: right"]19/01/2017[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl67, align: right"]27/05/2019[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl67, align: right"]17/08/2019[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl68, align: center"]FALSE[/TD]

</tbody>
[TABLE="width: 485"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 485"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Team[/TD]
[TD]Yellow[/TD]
[TD]Red[/TD]
[TD]Either Card[/TD]
[TD]Did the last match in which they got a card feature a red card[/TD]
[/TR]
[TR]
[TD="align: right"]06/04/2012[/TD]
[TD="align: right"]123456[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: right"]18/03/2015[/TD]
[TD="align: right"]123456[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: right"]19/01/2017[/TD]
[TD="align: right"]123456[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]27/05/2019[/TD]
[TD="align: right"]123456[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]17/08/2019[/TD]
[TD="align: right"]123456[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 485"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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


<tbody>
[TD="class: xl65"]Date[/TD]
[TD="class: xl65"]Team[/TD]
[TD="class: xl65"]Yellow[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]Either Card[/TD]
[TD="class: xl66"]Did the last match in which they got a card feature a red card[/TD]

[TD="class: xl67, align: right"]06/04/2012[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]-[/TD]

[TD="class: xl67, align: right"]18/03/2015[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65"]-[/TD]

[TD="class: xl67, align: right"]19/01/2017[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]

[TD="class: xl67, align: right"]27/05/2019[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]

[TD="class: xl67, align: right"]17/08/2019[/TD]
[TD="class: xl65, align: right"]123456[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[TD="class: xl68, align: center"]FALSE[/TD]

</tbody>
 
Last edited:
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
In that case, try the following instead...

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

Code: 
=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?
 
Upvote 0
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.
 
Upvote 0
Unless I missed it, I don't think you posted the actual formula you were using. What was your formula?
 
Upvote 0
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.

Code: 
=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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
By the way, my formula should be amended to use IFERROR instead...

Code: 
=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.
 
Upvote 0
Actually, I'm not a member of that forum. Can you just copy/paste the data? Then I'll test your formula.
 
Upvote 0
You must log in or register to reply here.

Similar threads

brockk
  • Solved
Undesired result - Can anyone figure out why the change of date referenced in formula.
Replies
5
Views
457
Fluff
Fluff
V
  • Solved
Show duplicates from across multiple ranges in one cell with a single nested formula?
Replies
9
Views
176
VikingLink
V
N
  • Solved
INDIRECT with an array argument--Formula doesn't work, but F9 gives correct result
Replies
3
Views
2K
Tetra201
Tetra201
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
787
Viau1337
V
M
  • Question Question
Wrong result when using <>
Replies
3
Views
828
Toadstool
Toadstool

Forum statistics

Threads
1,223,956
Messages
6,175,609
Members
452,660
Latest member
Zatman

Share this page

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