SWAT 11: VLOOKUP, Range References, and Application in HR and Encryption

SWAT 11: Comprehensive Guide to VLOOKUP and Data References

Learning Objectives and Administrative Details

• Course: COMP 110 Summer 2026.

• SWAT 11 Duration: $50$ to $90$ minutes.

• Key Dates:

  • Date Posted: $06/11/2025$

  • Date Due: $06/16/2025$

  • Accepted Until: $06/18/2025$

• Students Will Be Able To (SWBAT):

  • Understand the visual and logical process of searching down a column for a specific value and then moving right to extract related data.

  • Implement and troubleshoot the $VLOOKUP$ function.

  • Utilize and configure Named Ranges within spreadsheet formulas and functions.

Range References: Column and Row Notation

• Column References: To reference an entire column instead of a specific range like $A1:A1000$, you can remove the row numbers. For example, referencing the entirety of column A is written as $A:A$.

• Multiple Column References: You can reference a block of columns (e.g., all columns from A through C) using the syntax $A:C$.

• Row References: Similarly, rows can be referenced by removing the column letters. Adressing the entirety of row 1 is written as $1:1$ (instead of $A1:D1$).

• Worksheet References: When referencing ranges on different tabs, you must include the worksheet name followed by an exclamation point (e.g., $Amounts!D:E$).

Theoretical Foundation of Looking Up Data

• Conceptual Process: Lookups mimic a manual search process. Technology (voice recognition, Google, AI) has modernized this, but the fundamental logic remains similar to using a physical Table of Contents (TOC).

• The TOC Metaphor:

  • The Known Value: You must start with a piece of information you already know (e.g., Chapter $6$).

  • The Data Source: You require a physical Table of Contents containing the data.

  • The Visual Workflow: Your eyes start at the top left, move down the first column to find the known value (Chapter $6$), and then move horizontally to the right along the same row to find the related value (the Title or Page Number).

• Spreadsheet Mimicry: The $VLOOKUP$ function replicates this vertical movement (down a list) followed by horizontal movement (to the right).

Syntax and Arguments of the VLOOKUP Function

• Function Structure: $VLOOKUP(\text{search key}, \text{range}, \text{index}, \text{[is sorted]})$

• Argument Definitions:

  • search key: The piece of information currently known (e.g., the Chapter Number $6$). This is the value the formula searches for in the first column of the specified range.

  • range: The area containing the data.

    • The search key must exist in the leftmost (first) column of this range.

    • The range must be inclusive of all columns containing the needed information. If the search key is in column A and the result is in column D, the range must span $A:D$.

  • index: An integer representing how many columns to look to the right.

    • The index must be an integer greater than $1$.

    • The computer identifies columns by numbers ($1, 2, 3, 4$) rather than letters ($A, B, C, D$) within the defined range.

    • If the range is $A:D$, column A is index $1$, B is $2$, C is $3$, and D is $4$.

  • [is sorted]: A boolean value (TRUE or FALSE) indicating if the first column is sorted in ascending order. For the purposes of this course, this argument will always be FALSE.

Application: Table of Contents (TOC)

• Scenario: Look up the title and start page for Chapter $6$ (stored in cell $F1$) using a TOC spanning $A2:C15$.

• Worksheet Reference: Uses the $TOC$ worksheet.

• Formula for Chapter Title (Column B):

  • $=VLOOKUP(F1, A:C, 2, FALSE)$

  • Index is $2$ because the title is in the second column of the range $A:C$.

• Formula for Start Page (Column C):

  • $=VLOOKUP(F1, A:C, 3, FALSE)$

  • Index is $3$ because the page number is in the third column of the range $A:C$.

Application: Human Resources (HR) Calculations

• Bonus Eligibility: Determined by an $IF$ function in cell $E2$. If Years of Service are greater than or equal to $5$, display "Yes"; otherwise, "No".

• Performance Raise Calculation: Calculates the bonus by multiplying the current salary by a rate found in a separate table.

  • Formula in Cell F2: $=B2 * VLOOKUP(D2, Amounts!D:E, 2, FALSE)$

  • B2: Employee's current salary.

  • D2: Employee's performance rating (the search key).

  • Amounts!D:E: The range on a different worksheet where the rating-to-percentage table resides.

• Total Compensation:

  • Calculated in cell $G2$ using an $IF$ function.

  • Logic: If eligible for a bonus, total = Current Salary + Performance Raise + Bonus. If not eligible, total = Current Salary + Performance Raise.

Cryptography and Spreadsheet Encryption

• Historical Context: Julius Caesar used the "Caesar Cipher," a character replacement system where each letter is shifted by a set number of positions (e.g., A becomes D, B becomes E). This ensured messages intercepted between Rome and Scotland remained unintelligible.

• Modern Analogy: Website password security involves "encryption" (scrambling the password before it is sent to a server) and "decryption" (unscrambling it on the server to verify it against the stored value).

• Encryption Mechanism in Spreadsheets:

  • Key Sheets: Worksheets $Key1$ and $Key2$ contain mapping tables.

  • Column A: Original characters.

  • Column B: Replacement characters (used for encryption).

  • Column C: Original characters (paired with Column B to allow for decryption).

Encryption and Decryption Procedures

• Single Encryption (Single Encryption Worksheet):

  • To encrypt a character in cell $A2$: $=VLOOKUP(A2, Key1!A:B, 2, FALSE)$

  • This replaces the original character with a replacement from column B.

• Decryption:

  • To revert encrypted text in cell $B2$: $=VLOOKUP(B2, Key1!B:C, 2, FALSE)$

  • This looks for the scrambled character in column B and returns the original character from column C.

• Double Encryption (Double Encryption Worksheet):

  • Step 1: Encrypt original text with $Key1$ (Columns A and B).

  • Step 2: Encrypt the result of Step 1 again, typically using the same key or a different one.

  • Decryption: Must be performed in the reverse order of encryption to successfully retrieve the original message.

Practical Challenges: Inspiration, Treasure Hunt, and Video

• Inspiration Worksheet: Requires double decryption of a message in Column A using $Key2$ (Columns B and C) twice.

• Treasure Hunt Challenge:

  • Target String: $p 3 q i b , 4 e q 3 4 , 9 9 n$ (commas included).

  • Formatting Requirement: Numbers must be treated as text. This is achieved by typing a single apostrophe (') before the number (e.g., $'3$).

  • Goal: Test four combinations of $Key1$ and $Key2$ through double decryption to find a hidden classroom location.

• Secret Video Decryption:

  • URL Structure: $https://media.ithaca.edu/media/t/something$

  • The "something" is $7 f 3 o c 8 8 z p w$ (Note: characters are lowercase and include the letter "o", not the number zero).

  • Successful double decryption will yield a string starting with $1 \_$ (one underscore).

Submission Requirements

• Canvas Quiz: Answers to questions are derived from following the instructional steps in the Start File.

• Linking Work: The final quiz question requires a link to the completed spreadsheet.

• Sharing Settings: The spreadsheet must be set to "Anyone with the link can view" to ensure instructors can access the work for grading.