ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
How to Solve a Cryptogram Using Excel & VLOOKUP
3
A step-by-step guide using the VLOOKUP function to crack simple substitution ciphers (aristocrat ciphers)
4
5
What you're working with
6
This workbook uses a simple substitution cipher, the same kind found in newspaper cryptograms or puzzle books like Brain Games. Every letter in the hidden message has been consistently replaced with a different letter — for example, every 'F' in the puzzle always stands for the same real letter.
7
8
Step 1: Set up your key columns
9
On the 'Cryptogram Solver' tab, column A (labeled L1) lists every letter of the alphabet, A through Z. This represents the letters that can appear in the cipher. Column B (labeled L2) is where you'll enter what each letter actually translates to. Leave column B blank for now — you'll fill it in as you solve.
10
11
Step 2: Enter the cryptogram, one letter per cell
12
The encoded puzzle has been typed into row 33, one letter per cell, with blank/shaded columns marking the spaces between words. This layout makes it easy for a VLOOKUP formula to translate each letter individually.
13
14
Step 3: Build the VLOOKUP formula
15
Row 34 (directly under the puzzle) contains a VLOOKUP formula in every cell. Each formula looks up the cipher letter above it in the key table and returns your guess for what it stands for:
=VLOOKUP(C33,$A$4:$B$29,2,FALSE)
• Lookup value: the cipher letter in the cell above (e.g. C33)
• Table array: the key table, $A$4:$B$29 — the dollar signs lock the range so it doesn't shift when copied across
• Column index number: 2, because the translation (L2) is the second column of the key table
• Range lookup: FALSE, so it only returns an exact match instead of the closest one
16
17
Step 4: Solve it by filling in the key
18
Look for patterns: short words, repeated letters, common three-letter words like 'THE', and double letters. When you think a cipher letter stands for a specific real letter, type that real letter into column B next to it (e.g. next to 'T' in column A, type 'V' in column B if you think T = V). The moment you do, every matching letter in the puzzle below updates automatically — no manual erasing or rewriting needed.
19
20
Step 5: Refine as you go
21
If a guess turns out to be wrong, just delete or change the letter in column B — the puzzle will update instantly. Keep testing guesses against the words that are forming until the message reads clearly.
22
23
Tip: hide the zeros
24
Blank cells in the puzzle (the word-break columns) may show as 0 once formulas are added elsewhere. To hide these: File > Options > Advanced > scroll to 'Display options for this worksheet' > uncheck 'Show a zero in cells that have zero value'.
25
26
Note
27
This method works for simple substitution (aristocrat) ciphers only — where each letter always maps to the same replacement letter. It will not work for ciphers that use multiple symbols per letter or changing substitutions.
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100