Learning how to use LOOKUP in Excel unlocks one of the most powerful capabilities the spreadsheet offers: pulling matching information from one range and dropping it into another with a single formula. Whether you are reconciling invoices, pricing products, building dashboards, or merging two datasets that share a common identifier, lookup functions replace hours of manual searching with instant results. Modern Excel ships with several lookup tools, and choosing the right one for the job is the difference between a fragile workbook and a fast, reliable one.
The classic LOOKUP function is the simplest of the bunch. It searches a single row or column for a value and returns a result from a parallel row or column. Because it assumes your lookup vector is sorted in ascending order, it is best reserved for small, tidy datasets. When the data is unsorted, mixed in type, or you need to control match behavior precisely, you should reach for VLOOKUP, HLOOKUP, INDEX/MATCH, or the newer XLOOKUP function instead.
VLOOKUP, short for vertical lookup, is the most widely used member of the family. It searches the leftmost column of a table and returns a value from a column to the right that you specify. Its sibling HLOOKUP performs the same operation horizontally, scanning the top row of a table and pulling data from rows below. Both functions accept an optional fourth argument that toggles between approximate and exact matching, which is the source of nearly every lookup error beginners encounter.
XLOOKUP, introduced in Microsoft 365 and Excel 2021, was designed to fix the limitations of VLOOKUP. It can search in any direction, return values from columns to the left of the lookup column, default to exact matching, and even return a custom message when nothing is found. If you are running a modern version of Excel, XLOOKUP should be your default choice. If you are sharing files with users on older versions, INDEX and MATCH together offer nearly identical flexibility with universal compatibility.
This guide walks through each lookup function with practical examples, common error messages, and side-by-side comparisons so you can pick the right tool every time. We will cover exact versus approximate matching, wildcard searches, two-way lookups, error handling with IFERROR, and the dynamic-array behavior that changed how lookups work in modern Excel. By the end you will be comfortable building lookups across sheets, workbooks, and tables of any size.
Before diving in, take a moment to think about your data structure. Lookup formulas are only as reliable as the columns they search. Duplicate IDs, trailing spaces, mismatched data types, and merged cells are the four horsemen of lookup failures. We will revisit each of these pitfalls and show you how to spot and clean them so your formulas return the values you actually expect.
Lookup mastery also pairs well with related skills like learning how to freeze a row in excel when working with long tables so the headers stay visible while you scroll through thousands of records. With the foundations in place, let's break down each function and when to use it.
The original simple function that searches a sorted vector and returns a corresponding value. Best for small, sorted datasets where you need a one-line formula with no extra arguments.
Vertical lookup that searches the first column of a table and returns a value from a column to the right. The workhorse of business spreadsheets and the function most people learn first.
Horizontal version of VLOOKUP. Searches the top row of a table and returns values from rows beneath. Useful when your data is laid out across rows instead of down columns.
Modern replacement for VLOOKUP and HLOOKUP. Defaults to exact match, can return arrays, searches left or right, and includes a built-in if-not-found argument. Requires Excel 2021 or Microsoft 365.
A two-function combination that mimics XLOOKUP using older syntax. INDEX returns a value at a given position, MATCH finds the position. Universally compatible and very flexible.
The simplest way to learn how to use LOOKUP in Excel is to start with VLOOKUP, because almost every lookup concept transfers cleanly from there. The basic syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup_value is the thing you are searching for. The table_array is the range that contains your data. The col_index_num is which column in that range holds the result you want to return. The optional range_lookup is TRUE for an approximate match or FALSE for an exact match.
Suppose you have a product list in columns A through D with Product ID in column A, Product Name in B, Category in C, and Price in D. To pull the price for product ID P-104 typed in cell F2, you would write =VLOOKUP(F2, A:D, 4, FALSE). Excel scans column A looking for P-104, finds the row that contains it, and returns the value from the fourth column of the range, which is the price. The FALSE argument is critical here because it forces an exact match.
Mistakes most often come from forgetting that fourth argument. If you leave range_lookup blank or set it to TRUE, Excel assumes your lookup column is sorted alphabetically or numerically and returns the closest match below the lookup value. That works fine for tax brackets, grade boundaries, or shipping tiers, but it returns wildly wrong results for unsorted ID lists. As a rule of thumb, always type FALSE or 0 unless you are deliberately using approximate matching.
Another classic gotcha is the col_index_num. It refers to the column number within your table_array, not the column letter on the worksheet. If your table starts at column C and you want the value from column E, the col_index_num is 3, not 5. Inserting a column inside your table also shifts everything, which can silently break formulas. This is one reason many power users prefer XLOOKUP, which uses a return range instead of a column number.
To make your VLOOKUP formulas portable, lock the table reference with dollar signs or convert your data to an Excel Table. Writing =VLOOKUP(F2, $A$2:$D$1000, 4, FALSE) prevents the range from shifting when you copy the formula down. Even better, format the data as a table and use structured references like =VLOOKUP(F2, Products[#All], 4, FALSE), which automatically expand as you add rows and read more clearly during reviews.
HLOOKUP works identically except it scans the top row instead of the leftmost column and returns values from rows below. The syntax is HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). Because most real-world tables are laid out vertically, HLOOKUP comes up far less often. Reserve it for cases where headers run across the top and the values you want are stacked underneath, such as monthly columns in a budget summary.
Once you are comfortable with both directions, it becomes natural to extend them. Combining VLOOKUP with IFERROR replaces ugly #N/A errors with friendly text, and pairing it with MATCH lets you avoid hard-coding the column number. If you also need to add a filter in excel to narrow the dataset before lookups run, you will get cleaner results and faster recalculations on large workbooks.
VLOOKUP excel is the function most people learn first because it has been around since the earliest versions of the program and works in every edition. It is straightforward for lookups that move left-to-right within a contiguous table and where the lookup column sits on the far left. Almost every Excel course, certification exam, and finance interview includes at least one VLOOKUP question, so mastering it remains essential even if you eventually migrate to newer functions.
The downside of VLOOKUP is brittleness. It cannot search to the left of the lookup column, it breaks when columns are inserted, and the default approximate match catches countless users off guard. For one-off reports it is perfectly fine, but for production workbooks shared with colleagues, you should wrap it in IFERROR and use absolute references or structured table references to keep it stable as the workbook grows.
XLOOKUP is the modern replacement and the function Microsoft now recommends. The syntax XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) is cleaner and safer. It defaults to exact match, accepts separate lookup and return arrays so column insertion never breaks it, and has a built-in argument for handling missing values without nesting IFERROR around the formula.
You can also search from the bottom up, perform binary searches on sorted data, and return an entire row or column with a single formula thanks to dynamic arrays. The only catch is version compatibility. XLOOKUP exists in Microsoft 365, Excel 2021, and Excel for the web, but not in Excel 2019 or earlier. If your audience runs older versions, save XLOOKUP for personal workbooks and use INDEX/MATCH for anything you share.
INDEX/MATCH is the classic power-user combination. INDEX(return_range, row_number) returns a value from a position, and MATCH(lookup_value, lookup_range, 0) finds that position. Nesting them gives you a lookup that works in any direction, survives column inserts, and runs slightly faster than VLOOKUP on very large datasets because it only scans the columns you reference.
The syntax looks intimidating at first, with two function calls inside one formula, but the pattern is consistent. =INDEX(D:D, MATCH(F2, A:A, 0)) reads as get the value from column D in the row where column A equals F2. Once that pattern clicks, INDEX/MATCH becomes the most flexible lookup tool available to users on legacy Excel versions, and it remains popular even among Microsoft 365 users who built workbooks before XLOOKUP existed.
The number-one source of incorrect lookup results is the default approximate-match behavior of VLOOKUP, HLOOKUP, and LOOKUP. Unless you are intentionally bucketing numeric ranges such as tax brackets or grade thresholds, always type FALSE or 0 as the final argument. Better still, switch to XLOOKUP, which defaults to exact match and removes this entire class of silent errors from your workbooks.
Once you are comfortable with the basics, several advanced techniques will dramatically expand what you can do with lookup formulas. The first is the two-way lookup, where you search for both a row label and a column label at the same time. The classic pattern combines INDEX with two MATCH calls: =INDEX(B2:F20, MATCH("April", A2:A20, 0), MATCH("Revenue", B1:F1, 0)). The first MATCH finds the row, the second finds the column, and INDEX returns the cell at that intersection. XLOOKUP can do the same job by nesting one XLOOKUP inside another.
Wildcard lookups let you search using partial text. With VLOOKUP or XLOOKUP set to text-mode matching, you can use an asterisk to represent any number of characters and a question mark to represent exactly one. For example, =VLOOKUP("*Smith*", A:B, 2, FALSE) returns the value paired with the first entry that contains Smith. XLOOKUP requires you to set match_mode to 2 to enable wildcards, while VLOOKUP supports them by default whenever range_lookup is FALSE and the lookup value is text.
Approximate match is the workhorse for tiered calculations. Tax brackets, shipping fees, discount tables, and grade boundaries all share the same shape: a sorted list of thresholds paired with a result. With the lookup column sorted ascending, VLOOKUP or LOOKUP with TRUE returns the value associated with the largest threshold that does not exceed the lookup value. XLOOKUP achieves the same outcome with match_mode set to -1, which means find an exact match or the next smaller item.
Multi-criteria lookups handle the case where one key alone is not enough to identify a row. The cleanest modern approach uses XLOOKUP with concatenated arrays: =XLOOKUP(A2&B2, Sales[Region]&Sales[Product], Sales[Revenue]). This returns the revenue where region and product both match. In older Excel versions, the same job is done with INDEX and MATCH wrapped in an array formula, or by adding a helper column that concatenates the keys before the lookup runs.
Error handling polishes any lookup formula. Wrapping VLOOKUP with IFERROR turns #N/A into a friendly message: =IFERROR(VLOOKUP(F2, A:D, 4, FALSE), "Not found"). XLOOKUP makes this even cleaner with its native if_not_found argument: =XLOOKUP(F2, A:A, D:D, "Not found"). For finer control, IFNA only catches #N/A while letting genuine errors like #REF! propagate so you can fix them rather than mask them.
Dynamic-array behavior in modern Excel changes how lookup results spill. If you give XLOOKUP an array of lookup values, it returns an array of results that automatically fill adjacent cells. Combined with FILTER, SORT, and UNIQUE, this enables one-formula reports that previously required dozens of nested functions. Just be aware that spilled formulas need empty cells below and to the right; if anything blocks them, you get a #SPILL! error.
Finally, performance matters on large workbooks. Full-column references like A:A force Excel to evaluate over a million rows even when only a few hundred contain data. Limit your ranges to the actual data, convert to Excel Tables, and consider switching from VLOOKUP to INDEX/MATCH or XLOOKUP on workbooks with tens of thousands of rows. The speed difference can drop recalculation times from minutes to seconds.
Let's walk through three real-world scenarios that show how lookup functions solve everyday problems. First, picture a small e-commerce shop that maintains a product master in one sheet and daily order exports in another. Each order row contains a SKU but no product name or price. A single XLOOKUP formula in the order sheet, =XLOOKUP([@SKU], Products[SKU], Products[Price], 0), instantly enriches every order with the correct price. The same pattern applies to lookup_value categories, weights, or supplier names, eliminating manual matching entirely.
Second, consider an HR analyst building a salary band lookup. The lookup table lists minimum salary thresholds for each grade. To find the grade for any salary, use =VLOOKUP(B2, Grades, 2, TRUE) on a sorted thresholds column. Approximate match returns the grade whose threshold is the largest value not exceeding the salary. This pattern works equally well for tax brackets, commission tiers, shipping fees by weight, and quantity-based discount schedules, so once you learn it you will reuse it constantly.
Third, imagine a finance team consolidating regional reports. Each region submits a workbook with the same template but different numbers. A summary file uses XLOOKUP to pull the relevant line item from every regional file, with the file path embedded in the formula. When a regional analyst updates their workbook, the summary recalculates automatically the next time it opens. INDEX/MATCH does the same job for teams stuck on Excel 2019, and IFERROR provides graceful handling when a regional file has not yet been delivered.
These examples highlight a common pattern: lookups are most powerful when the source data is clean and consistent. Before building any meaningful lookup, take five minutes to inspect the lookup column for duplicates, trailing spaces, and inconsistent capitalization. Functions like TRIM, CLEAN, UPPER, LOWER, and PROPER standardize text, while VALUE and TEXT convert between numbers and strings. A well-prepared dataset turns lookups from a source of bugs into a source of leverage.
Lookups also integrate beautifully with other features. Pair an XLOOKUP with a drop-down list so users choose values from a controlled vocabulary instead of typing freely, which prevents typos that break formulas. Combine lookups with conditional formatting to highlight rows where a match was not found. Connect them to PivotTables so summary calculations always reflect the latest reference data. Each of these combinations is straightforward once the underlying lookup works correctly.
A frequent companion technique is learning how to count unique values in excel, because lookups generally assume unique keys in the source range. Counting uniques is the fastest way to discover whether your lookup column has duplicates that could return inconsistent results. If duplicates exist, decide whether to deduplicate the source, concatenate additional keys, or use XLOOKUP's search_mode argument to control which match is returned.
Finally, document your lookups. A short comment or a named range like Products_Lookup makes formulas readable months later. Use the Name Manager to label key ranges, and consider building a small data dictionary on a hidden sheet that lists the purpose of every lookup table. These habits cost almost nothing to adopt and pay dividends every time you or a colleague has to debug, extend, or audit the workbook.
To wrap up, here is the practical workflow we recommend whenever you sit down to build a lookup. Step one, identify the lookup value, the source table, and the column you want to return. Step two, check your Excel version: if you have Microsoft 365 or Excel 2021, reach for XLOOKUP; otherwise default to INDEX/MATCH or a well-written VLOOKUP. Step three, lock your ranges with absolute references or convert your data into an Excel Table for automatic expansion. Step four, always specify exact match unless approximate is truly what you need.
Step five, test the formula on at least three sample rows before copying it down. Include one row that you know has a match, one row that should not match at all, and one row where the lookup value contains tricky characters like apostrophes, accented letters, or numeric strings. If all three return the expected result, you can trust the formula across the rest of the dataset. If any fail, isolate the problem before scaling up.
Step six, wrap the formula in IFERROR or use XLOOKUP's if_not_found argument so missing values display a clean message instead of #N/A. This single habit transforms how professional your workbooks look to stakeholders. Step seven, document the formula's intent with a comment or a clearly named range, so the next person who opens the file can understand it without reverse-engineering each cell.
For longer-term maintenance, periodically audit your lookup columns for duplicates and data-type consistency. Set up data validation drop-downs on cells that feed lookup formulas, so users cannot enter values that will not match the source. When source data comes from external systems, use Power Query to clean, trim, and type-cast columns automatically every time you refresh. These guardrails prevent the slow drift that turns a working spreadsheet into a maintenance nightmare a year later.
If you are preparing for an Excel certification or a job interview, expect lookup functions to feature prominently. MOS Excel Expert, Excel Associate, and most finance and data analyst interviews include at least one lookup question, often requiring you to choose between functions and explain trade-offs. Practice writing the syntax from memory, recognize the common error messages, and be ready to walk through how you would diagnose a misbehaving formula step by step.
The good news is that lookup mastery compounds. Once VLOOKUP feels natural, INDEX/MATCH and XLOOKUP follow quickly because the mental model is the same: find a key, return a related value. From there, you will start spotting opportunities to replace tedious manual work everywhere — in reports, dashboards, reconciliations, customer lists, inventory feeds, and project trackers. Lookups become the connective tissue that turns isolated tables into a single integrated data model.
Keep practicing with real datasets rather than contrived examples. Download your bank statements, sports stats, or open government data and try to enrich them with lookups. The friction you encounter — duplicates, dirty text, mismatched types — is exactly what you will face in the workplace, and solving those puzzles is what turns Excel users into Excel power users. The functions are simple; the craft is in applying them cleanly.