Recently, I’ve been auditing my own workflow to figure out the "best practices" I subconsciously use for common Excel functions. I wanted to share the 5 golden rules I always follow when using VLOOKUP to keep my sheets from breaking.

1) Keep Lookup Tables Internal

Always try to keep your lookup tables in the same workbook as your formulas. Linking to an external file on a shared drive is a recipe for disaster—if someone renames, moves, or deletes that source file, your formulas will instantly break and return #N/A or #REF! errors. Keeping everything in one file guarantees your paths stay intact.

2) Expand Your Table Arrays

Expand your table arrays to include more Rows and Columns then what is currently needed. This way, you will never have to update your formula when you add data to your lookup table. This is especially helpful with Rows as it is more likely you will add data underneath column headers once addition data is required.

3) Master Your Absolute References (F4)

Don't forget to lock your cells!

• Lock your Table Array: Hit F4 once ($A$1:$B$10) so the reference table doesn't shift when you drag your formula.
• Lock your Lookup Value (if dragging horizontally): Hit F4 three times ($A1) to lock just the column. This ensures your formula always looks at the correct identifier, no matter where you copy and paste it.

4) Use Named Ranges for Clean Formulas

Naming your table array makes writing and reading formulas incredibly easy. For example, if you have a lookup table for weather data, select the data and name the range "Seasons". Your formula goes from messy cell coordinates to a clean, readable sentence: =VLOOKUP(A2, Seasons, 2, FALSE)

5) Wrap it in IFERROR

If you know your data will have legitimate non-matches, don't leave your sheet covered in ugly #N/A errors—it can make your data integrity look questionable to managers or clients. Wrap your VLOOKUP in an IFERROR statement to clean it up: =IFERROR(VLOOKUP(A2, Seasons, 2, FALSE), "No Match")

I actually put together a short video demonstrating these tips in action if you want a visual walkthrough: Watch the VLOOKUP Guide on YouTube

What are your personal VLOOKUP rules? Or have you completely migrated to XLOOKUP/Index-Match yet?

Hi Everyone,

Recently I came across an issue at work where I had a long list of unique identifiers in a single column, and I needed to number each item sequentially as it appeared from top to bottom.

The Problem: Imagine you have a column listing different types of fruits. If "Apple" shows up 5 times throughout the sheet, you want the first occurrence to return 1, the second to return 2, and so on. In that same column, "Pear" might appear 3 times, and should be numbered 1, 2, and 3.

The Solution: The trick is using an "expanding range" COUNTIF formula. Assuming your data starts in cell A2, enter this in cell B2 and drag it down:

=COUNTIF($A$2:A2, A2)

I made a quick video with step-by-step directions showing this workflow in action: https://www.youtube.com/watch?v=4zlkcxQslRk

Why this works: By using the absolute reference ($A$2) for the start of the range, but leaving the end of the range relative (A2), the formula creates an expanding net. As you copy it down, it only counts how many times that specific fruit has appeared up to that specific row.

I hope this helps save some of you a few headaches! Let me know if you use this trick or have a different way of handling duplicate rankings. Take care!

Quick Visual:

Column A (Data)	Column B (Formula Result)
Apple	1
Pear	1
Apple	2
Orange	1
Apple	3
Pear	2

Hey Everyone,

Most people only use XLOOKUP for basic vertical lookups, but it’s essentially a superpower that can completely replace VLOOKUP, HLOOKUP, and INDEX/MATCH.

Here is a quick breakdown of the advanced features built directly into its syntax [=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])]:

• Horizontal Lookups: Because it separates the lookup array from the return array, it handles horizontal data seamlessly without needing a separate function.
• Built-in Error Handling ([if_not_found]): You no longer need to wrap your formulas in =IFERROR(). You can type your error message (like "Not Found") directly into the 4th argument.
• Wildcard Matching ([match_mode] = 2): You can look up partial text strings using asterisks (e.g., searching "*Corp*" to find "Microsoft Corp").
• Search Modes ([search_mode]): You can force Excel to search from the bottom up (-1) or leverage binary searches (2 or -2) for massive datasets.

I put together a comprehensive, step-by-step video breakdown demonstrating exactly how to apply each of these arguments with real-world workbook examples:

https://www.youtube.com/watch?v=FWVAmMwNN58

Hope this helps streamline your workflows! Let me know if you have any questions on setting up these specific match modes.