How to Solve a Cryptogram Using Excel & VLOOKUP

In this video, I show you how to solve a basic cryptogram using the VLOOKUP function in Excel. We'll do that by entering the cryptogram into Excel, one letter per cell, then we'll create our own key and use VLOOKUP to figure out the puzzle. Don't worry if you're not very familiar with VLOOKUP - I will walk you through it and explain it. This might actually help you better understand how it can be used.
This video is meant to be used with cryptograms that use simple substitutions, basically the same replacement letter for each different letter in the cipher. I believe these are called aristocrat ciphers, or at least they're very similar to those.
To quickly split sentences so each character is in its own cell, just enter the sentence into cell A1 and paste this formula =MID($A1, COLUMNS($A$1:A$1), 1) in cell B1 and copy/drag it across row 1 until each character appears in its own cell. Much easier than manually entering it! Here's a video explaining two ways to do this: • 2 Ways to Split a Stri...
I did not receive any compensation from the products used in this video, I just enjoyed using them and thought it would be relevant for my channel. If you're interested in the products I mentioned, they can be found here on Amazon:
-Hunt a Killer: Death at the Dive Bar - amzn.to/2Tx5PY4
-Brain Games: Cold Case Puzzles - amzn.to/3oropPo
Don't forget to subscribe to my channel for more tutorials!
Check out my website at simplesoftwaretutorials.com
Video recorded using Screencast-O-Matic (www.screencast-o-matic.com)
Thanks for tuning in!

[@kendavies8127]

Excellent video! Thanks for this! I made a much more complicated version, not being familiar with the VLOOKUP function. My question is: Is there any way to make the same function use rows instead of columns? In other words, have the letters running across the top of the page (horizontally vs. vertically)? I found myself needing to scroll up and down in order to enter the "answer" letters.

[@kendavies8127]

Nevermind, I just found HLOOKUP. : )

[@SimpleSoftwareTutorials]

I'm so glad you liked my video! Yes, there's a function called HLOOKUP that does just that. I pulled out the spreadsheet I used for this video and tested it out.
Here's how I did it:
-I have letters A-Z entered from A1 through Z1
-My answer letters will go in the cells below on row 2
-I started my cryptogram in cell E5, so my formula will go in the cells below
-I changed the VLOOKUP formula to =HLOOKUP(E5,$A$1:$Z$2,2,FALSE)
To explain the formula:
-E5 is the cell I want my formula to check for the letter I'm trying to solve. This will update as I copy and paste the formula across the rows
-$A$1:$Z$2 is the range I want the formula to look up. The $ will keep that range constant as I copy and paste the formula.
-2 refers to the second row - that's where my solution letters will be entered.
-False tells the formula to only find exact matches. It may not be relevant for this example, but I like to enter it anyway.
I hope this helps!

[@nkeerthana2987]

I have a cryptogram code. I could not resolve it. I need your help