CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulavlookup
LuisAFK
LuisAFK

Reputation: 957

VLOOKUP in Google Sheets returns the wrong thing

So I have a Google Sheet and I'm trying to make a Rock, Paper, Scissors game. Everything works for now, but I'm missing a way to check who wins. I thought of having a range of cells and then use a VLOOKUP to find the winner. But it sometimes returns the wrong thing.

The cells are these:

Range of cells

The left and middle are the ones to check through, and the one on the right would be the winner.

I tried this: =IF(B2 = J2, "Tie!", VLOOKUP({B2, J2}, X1:Z3, 3, FALSE)) where B2 and J2 are the player's choices, and X1:Z3 is the range mentioned above. It works most of the time, but sometimes it returns the wrong thing, like:

Returns the wrong thing

Everyone knows that Rock wins Scissors, so that is wrong.

Upvotes: 0

Views: 54

Answers (1)

Ramayana 3
Ramayana 3

Reputation: 131

VLOOKUP cannot search for multiple search_keys. So, you can combine the search key into a single search_key. I use b2&j2 So we can modify formula and table

=IF(B2 = J2, "Tie!",VLOOKUP(B2&J2, ARRAYFORMULA({X1:X6&Y1:Y6,Z1:Z6}), 2, FALSE))

enter image description here

Please review it: https://docs.google.com/spreadsheets/d/1XGCLY8-lkozE7Fj-ZWmHHnTcMx7XjZJWPB9PWxHLAOs/edit?usp=sharing

Upvotes: 3

Related Questions