While a big vocabulary is an important part of Scrabble success, an arguably more important Scrabble “skill” is memorizing all of the two-letter, and at a more advanced level, three-letter words. A well-played “QI” or “XI” or “QAT” can be the difference between winning and losing. This sheet is the adult version of last week’s Sight Words Quiz for kids.
In today’s post, we’ll build a quiz in which you can type in the first, second, or third letter, and the spreadsheet will return a random two or three letter word that matches the input.
Spreadsheet inputs
We’ll use a complete list of two and three letter words using this website. We’ll also add in a little logic in columns B-D that breaks out each word into its individual three letters, which we’ll need for later:
Note that we’ll start the data on line 15, since we’ll likely have our interface and some calculations in row 1-13. We’ll have separate tabs for our two-letter and three-letter quizzes. We went with Google Sheets so that in theory you could pull this sheet out on your phone during some downtime on the subway…whenever we’re able to ride those again…
Spreadsheet Calculations and Output
You can enter in any of the three letters in the words you want to pull, then put in some logic to assign a 1 to the words that match the user inputs and a 0 if they don’t match.
Here’s my attempt to explain what I did: Columns E,F,G check to see if each of your letters match the current word, and returns a 1 if it matches, 0 if it doesn’t. Column H checks to see if all three letters matched by seeing if E,F,G sum to 3. Column I assigns a unique number to each match, and then Column J cleans up Column I by setting all the non-matches to 0. Columns K and L look up all the matches in order.
It’s probably better understood if you download the spreadsheet and walk through the formulas.
Here’s what it looks like, and I pasted in what the key formulas look like in rows 11 and 12:
Note that this sheet provides a quiz, where one word is randomly drawn, but you can also just scroll through column L to see all the possible Scrabble words that match the search criteria.
Download the spreadsheet here (reminder, you can Make a copy of the spreadsheet and save it to your Google Drive by going to FIle –> Make a Copy:
Google Spreadsheet: Spreadsheet Solving Scrabble Quiz
Here’s a Google Sheets tip: To generate a random number, you have to recalculate the Sheet. In Microsoft Excel, you just have to hit F9 to recalculate. In Google Sheets you can just hit Ctrl+R in any cell, which effectively copies the cell to itself, but also has the benefit of recalculating the sheet and generating a new random quiz word.
