Webbo3 Data Analysis Bootcamp · Excel Module · Lesson 9

Excel Lookup Functions: VLOOKUP, HLOOKUP, XLOOKUP, and INDEX with MATCH

A hands on lesson on finding and pulling data from tables, the genuine errors that trip up almost everyone, and the modern functions that fix VLOOKUP's oldest weaknesses.

Almost every real spreadsheet eventually needs to answer one specific kind of question: given a piece of information I already have, what is the related piece of information sitting somewhere else in my data. A customer ID needs to become a customer name. A product code needs to become a price. A student ID needs to become a grade. Lookup functions exist purely to answer that question, and this lesson covers the four genuinely important ones, in the order most people actually meet them in real Excel work.

1. VLOOKUP: Exact Match and Approximate Match

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP searches down the first column of a range you give it, looking for a match to your lookup value, and once it finds that match, it returns a value from the same row but a different column, counting that column position from the left edge of your range. If A2:C50 holds employee ID in column A, name in column B, and department in column C, =VLOOKUP(E2,$A$2:$C$50,3,FALSE) finds the employee ID matching E2 and returns their department from the third column of that range.

The fourth argument is the single most important decision in the entire formula. Setting range_lookup to FALSE tells VLOOKUP to search for an exact match only, and if it cannot find one, it returns a clear #N/A error so you know immediately that something did not match. Setting it to TRUE, or simply leaving it out entirely, tells VLOOKUP to perform an approximate match instead, finding the closest value that is less than or equal to your lookup value. Approximate match genuinely requires your lookup column to be sorted in ascending order, and is the correct choice for range based lookups like tax brackets or commission tiers, where you want "anything between 50,000 and 99,999 falls into this bracket" style logic.

The single most dangerous default in all of Excel. If you omit the fourth argument entirely, VLOOKUP does not error out or ask you to clarify. It quietly defaults to approximate match. On unsorted data, this can return a confidently wrong answer with no error message at all, which is far more dangerous than an obvious #N/A, since a wrong number that looks correct can sit undetected in a report for a long time. The practical rule, with no exceptions: always type FALSE explicitly as the fourth argument unless you have a specific, deliberate reason to use TRUE for a genuine range based lookup, and never simply leave that argument blank.

2. HLOOKUP: Horizontal Lookup

Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

HLOOKUP is VLOOKUP rotated ninety degrees. Instead of searching down the first column and returning a value from a column to the right, HLOOKUP searches across the first row of your range and returns a value from a row further down, counting that row position from the top of your range. This matters specifically when your data is laid out with categories running across the top as column headers rather than down the side, for example monthly figures running left to right across row 1, with each row beneath holding a different metric. =HLOOKUP("March",B1:M5,3,FALSE) finds "March" somewhere in the header row and returns the value from the third row down in that same column. Every rule from VLOOKUP applies identically here: FALSE for exact match, TRUE only for sorted, range based approximate matches, and never leaving the fourth argument blank by accident.

3. Common VLOOKUP Errors and How to Fix Them

#N/A, the most common error. This means Excel genuinely could not find your lookup value anywhere in the first column of your range. Before assuming the value is simply missing, check for the quieter causes first: stray leading or trailing spaces around the lookup value (fix with =VLOOKUP(TRIM(A2),...)), a number stored as text instead of a real number, or the value being typed slightly differently between the two locations.

#REF!, almost always a column count problem. This appears when your col_index_num points to a column position that does not actually exist inside your table_array, for example asking for column 5 when your range only spans 3 columns. This frequently happens after the fact too, when someone later deletes a column from inside your original table_array, shifting every column position without you touching the formula at all.

#VALUE!, usually a wrong data type or a number that is too large. This can appear if col_index_num is not a positive whole number, or in older Excel versions, if your lookup_value text exceeds 255 characters.

#NAME?, almost always a typo. This means Excel does not recognise the function name you typed at all, most commonly because of a simple misspelling like "VLOKUP" instead of "VLOOKUP."

The most dangerous category: a wrong result with no error at all. This happens in two specific situations. First, if your lookup column has duplicate values, VLOOKUP always returns only the very first match it finds, silently ignoring every other matching row that might exist further down. Second, if you are using approximate match (TRUE) on data that is not actually sorted in ascending order, VLOOKUP can return a confidently wrong value without throwing any error, exactly the dangerous default behaviour covered in the VLOOKUP section above.

The single habit that prevents most of these. Always lock your table_array as an absolute reference with dollar signs, for example $A$2:$C$50, before copying a VLOOKUP formula down a column. Without that lock, copying the formula shifts the range along with it, which can quietly cut off rows of real data or pull in blank cells below your actual table, producing exactly the kind of wrong-but-not-obviously-wrong result that is hardest to catch.

4. XLOOKUP: The Modern Replacement for VLOOKUP

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP was built specifically to fix VLOOKUP's oldest, most well known weaknesses, and it succeeds at most of them. Instead of one combined table_array plus a column index number, you supply two completely separate ranges: lookup_array, where Excel searches, and return_array, where it pulls the matching result from. =XLOOKUP(E2,A2:A50,C2:C50) searches A2:A50 for a match to E2, and returns the corresponding value from C2:C50, with no column counting required at all.

Three genuine improvements that solve real, longstanding VLOOKUP problems. First, XLOOKUP defaults to exact match, the safer behaviour, completely reversing VLOOKUP's dangerous default of approximate match. Second, XLOOKUP can look both left and right, since your return_array can sit anywhere relative to your lookup_array, fixing VLOOKUP's strict left-to-right-only restriction. Third, because lookup_array and return_array are separate, fixed ranges rather than one combined table with a column position counted from the left, inserting or deleting a column elsewhere in your sheet does not silently break an XLOOKUP formula the way it would break VLOOKUP's col_index_num.

Built in error handling, no extra wrapping required. The optional fourth argument, if_not_found, lets you specify directly inside the XLOOKUP formula itself what to return when nothing matches, for example =XLOOKUP(E2,A2:A50,C2:C50,"Not found"), with no need to separately wrap the whole thing inside IFERROR or IFNA the way VLOOKUP requires. One genuine limitation worth knowing: XLOOKUP only exists in Excel 2021, Microsoft 365, and Excel for the web. If you ever share a workbook with someone using Excel 2019 or older, any XLOOKUP formulas will show a #NAME? error on their machine, and VLOOKUP or INDEX with MATCH remain the safer, fully backward compatible choices in that situation.

5. INDEX and MATCH Combined

MATCH syntax: =MATCH(lookup_value, lookup_array, [match_type])

INDEX syntax: =INDEX(array, row_num, [column_num])

These two functions solve the lookup problem from a completely different angle than VLOOKUP or XLOOKUP, and understanding that angle explains why this combination has stayed popular for decades even after XLOOKUP arrived. MATCH does not return the matching value itself. It returns a position, a plain number telling you where inside a range your lookup value was found. =MATCH("Tottenham",C4:C13,0) might return 5, meaning "Tottenham" sits in the fifth position of that range, with the third argument set to 0 meaning an exact match is required. INDEX then takes that position number and retrieves the actual value sitting at that exact spot inside a different range. =INDEX(D4:D13,5) returns whatever value sits in the fifth position of D4:D13.

Combined into one formula, MATCH finds the position and INDEX retrieves the value at that position. =INDEX(D4:D13,MATCH("Tottenham",C4:C13,0)) does in one formula exactly what the two separate steps above did across two cells. This combination shares two of XLOOKUP's biggest advantages over VLOOKUP, it can look in any direction, left or right, and it is not sensitive to columns being inserted or deleted, since it never relies on a hardcoded column position the way VLOOKUP's col_index_num does. It also works in every version of Excel ever made, including very old ones that have neither XLOOKUP nor Excel Tables, which is the main reason it remains worth learning even now. The genuine trade off: unlike XLOOKUP, INDEX and MATCH has no built in error handling at all. A failed match simply returns a plain #N/A with no custom message option built into the formula itself, so you still need to wrap the whole combination inside IFNA, for example =IFNA(INDEX(D4:D13,MATCH(E2,C4:C13,0)),"Not found"), to get the same friendly fallback message XLOOKUP gives you for free.

Using AI to Move Faster in Excel

Lookup formulas are where the gap between knowing the syntax and choosing the right tool for the job becomes most visible, and this is exactly where AI inside Excel earns its place, helping you pick correctly rather than just typing faster.

1. Describe the actual lookup, let Copilot choose between VLOOKUP, XLOOKUP, and INDEX with MATCH.
Instead of deciding upfront which function fits your exact situation, especially when you need to look to the left of your lookup column, or you are unsure whether your Excel version supports XLOOKUP, describe the real task directly, for example "Find this employee's department based on their ID, where the department column sits to the left of the ID column." Copilot will typically reach for INDEX and MATCH or XLOOKUP automatically, exactly the situation where plain VLOOKUP would fail outright, and explain why it avoided VLOOKUP for that specific case.

2. Ask Copilot to diagnose a lookup that returns a wrong value with no error.
Since the most dangerous lookup failures produce a confident wrong answer rather than a visible error, this is exactly the kind of problem worth asking about directly: "This VLOOKUP seems to be returning the wrong department for this employee ID, but there's no error. What could be causing that?" A good response should check for exactly the causes covered in this lesson: duplicate lookup values, unsorted data combined with an approximate match, or a hidden data type mismatch.

3. Use AI to convert an old VLOOKUP into a more robust XLOOKUP or INDEX MATCH formula.
If you inherit a workbook full of VLOOKUP formulas that break every time a column gets inserted, select a cell and ask "Rewrite this VLOOKUP formula using XLOOKUP so it won't break if columns are added or removed." This is a safe, easily verifiable use of AI, since you can run both formulas side by side on the same data before replacing the fragile original.

4. Never trust a lookup result just because it returned a value instead of an error.
This is the single most important habit in this entire lesson, and it applies regardless of who or what wrote the formula. A lookup returning a plausible looking value is not the same as a lookup returning the correct value. Whether you built the formula yourself or Copilot generated it, manually verify a handful of results against rows you can check by hand, particularly checking for duplicate lookup values and confirming the match type, FALSE versus TRUE, was the deliberate choice you actually intended, not an accident.

A habit worth carrying from this lesson onward: before writing or prompting for any lookup formula, ask yourself three things out loud. Does my lookup value definitely exist exactly once in the source data. Does my return value sit to the right of my lookup value, or do I need a function that can look left. And am I deliberately choosing exact or approximate match, rather than letting Excel choose the dangerous default for me. Those three questions, asked every single time, prevent the overwhelming majority of lookup mistakes covered in this lesson.

Next lesson: working with data ranges, sorting, filtering, and conditional formatting.