Advanced Formulas, Automation, and Protection

Beyond VLOOKUP: The Power of INDEX and MATCH

While VLOOKUP is useful, it has two major limitations: it can only look for values in the first column of a table, and it can only return a value to the right. The combination of INDEX and MATCH is more powerful and flexible.

• INDEX(array, row_num, [column_num]): Returns the value of a cell in a table based on the row and column number.
• MATCH(lookup_value, lookup_array, [match_type]): Searches for a value in a range and returns its relative position (i.e., the row or column number). Use 0 for an exact match.

By using MATCH to find the row number for INDEX, you can look up values in any direction.

The Magic Formula: =INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

Example: You want to find the “Department” (in column B) for an employee ID located in cell G1 (lookup column is A). VLOOKUP cannot do this because it has to look to the left.

=INDEX(B:B, MATCH(G1, A:A, 0))

This formula finds the position of the employee ID in column A, and then INDEX returns the value from that same position in column B.

The Flexible OFFSET Function

The OFFSET function is used to return a reference to a range that is a specified number of rows and columns from a starting cell or range. It’s incredibly powerful for creating dynamic ranges that automatically adjust as your data grows.

Syntax: =OFFSET(reference, rows, cols, [height], [width])

• reference: Your starting point.
• rows: How many rows to move down (positive) or up (negative).
• cols: How many columns to move right (positive) or left (negative).
• [height] / [width]: (Optional) The height and width of the range you want to return.

Example: To get the value from the cell 3 rows down from C3 and in the same column:

=OFFSET(C3, 3, 0)

Introduction to Automation with Macros

A Macro is a recorded sequence of actions, clicks, and keystrokes that you can save and run again and again. It’s the ultimate tool for automating repetitive tasks.

How to Record a Macro:

1. First, you may need to enable the Developer tab. Go to File > Options > Customize Ribbon and check the box for Developer.
2. On the Developer tab, click Record Macro.
3. Give your macro a name and an optional shortcut key.
4. Choose where to store it:
   • This Workbook: The macro is saved with the file. You must save the file as a Macro-Enabled Workbook (.xlsm).
   • Personal Macro Workbook: The macro is saved on your computer and is available to use in any Excel file you open.
5. Perform the actions you want to automate (e.g., apply specific formatting, sort data).
6. On the Developer tab, click Stop Recording.

You can now run this macro using the shortcut key or by going to Developer > Macros, selecting it, and clicking Run.

Protecting Your Work

Once your spreadsheet is complete, you may want to protect it from accidental changes.

• Protect Sheet: Go to the Review tab and click Protect Sheet. This locks all cells by default. To allow users to edit some cells but not others, you must first select the cells they can edit, right-click, choose Format Cells, go to the Protection tab, and uncheck the Locked box. Then, protect the sheet.
• Protect Workbook: Also on the Review tab, this prevents users from changing the workbook’s structure (e.g., adding, deleting, or renaming sheets).
• Encrypt with Password: For the highest level of security, go to File > Info > Protect Workbook > Encrypt with Password. This prevents anyone from opening the file without the password. Be careful, as there is no way to recover a lost password!