Excel Error with VLOOKUP using concatenation

Question

I built a spreadsheet for my math class to model the "Monty Hall" probability problem (deals with conditional probability). I didn't want to dig in with VBA so I tried this with all formulas.

The problem itself is modeled after the gameshow "let's make a deal" wherein a contestant chooses 1 of 3 doors to find a hidden prize. The host (Monty) then opens an empty door (not the one the player picks). The question is whether the contestant should switch their original choice to the remaining door. The answer is yes with a 2/3 probability of success.

To model this in excel, I have 4 columns: "Prize hidden", "You pick", "Monty Opens", and "Pays to switch?". Columns A and B use RANDBETWEEN(1,3) and the rest are logical matching. The toughest one is "Monty Opens". This is where I have a bug.

Here is a screenshot: https://i.stack.imgur.com/o5tWb.jpg

The "Monty Opens" column has to check the values of the first two columns then return a value based on where the prize is hidden and which door the player chooses. I used IF logic along with VLOOKUP. If the player chooses the door with the prize, then I used the smaller array to provide the empty door. If the player's original choice and the prize door are different (happens with 2/3 probability), then Monty has to open a third door that cannot be either of the first two. That scenario is harder with VLOOKUP. After some trial and error, I constructed an array with a "helper column" with concatenated values for "Prize Hidden" and "You Pick" in the far-left column. This is designed to "trick" VLOOKUP into looking at two columns at once. It seems to be working with the exception of the 3-1 case. It should match 3-1-2 but is throwing 3-1-1 and I can't understand why. After some troubleshooting, I'm pretty sure it's matching the concatenated values of 3-1 to the 23 row, thus returning "1" for column 4. I'm really unsure why this is happening as it seems to working for the other cases.

I'm realizing a complex IF statement might not be the best and I'm guessing there's some simple VBA, but now that I'm down the rabbit hole...

Thanks for reading and I appreciate any suggestions.

Answer 1

Not getting into the logic, I can already see 2 possible syntax mistakes.

1. VLOOKUP needs a fourth parameter set to FALSE to get exact values only. I asume that's the case for you. Refer to help for futher info.
2. VLOOKUP needs necessarily the first column of the table (2nd parameter) sorted. Maybe that's what is causing the wrong value in the final row.
   Check these and come back if the problem persists.

Answer 2

Excel issue: the reason you get the answer wrong is the missing 4th parameter. You have to tell VLOOKUP to search for the exact value. The correct formula should be:
=IF(A2=B2,VLOOKUP(A2,$L$5:$M$7,2,FALSE),VLOOKUP(A2&B2,$K$10:$N$15,4,FALSE))

Logical issue:
Your answer works fine if the doors differ between the prize and your pick because Monty has only one option. But if the doors are the same, Monty always selects the same door. You should be consistent and randomize Monty's selection.

Suggestions:

1. I would use this formula:
   =LET(totalDoors,SEQUENCE(3), doorsLeft,FILTER(totalDoors,(totalDoors<>A2)*(totalDoors<>B2)), INDEX(doorsLeft,RANDBETWEEN(1,COUNT(doorsLeft))))
   Please tell me if you need help understanding it.

2. If you do not care to randomize Monty's choice, this formula is simpler:
   =MAX(SEQUENCE(3)*(SEQUENCE(3)<>A2)*(SEQUENCE(3)<>B2))