INDEX and VLOOKUP in Excel: How They Work and Compare

Formulas & functions · Updated November 18, 2026

INDEX and VLOOKUP are both Excel lookup tools: VLOOKUP searches the leftmost column of a range and returns a value to its right, while INDEX returns a value at a specified position and is often paired with MATCH for a flexible lookup. Use VLOOKUP for a straightforward table search; choose INDEX with MATCH when the return column may move, sits to the left of the search column, or needs to be selected dynamically.

What INDEX and VLOOKUP Do

VLOOKUP means vertical lookup. It searches down the first column of a selected range for a value, then returns a value from another column in the same row. For example, a product list might have product IDs in column A, names in column B, and prices in column C. A VLOOKUP can find an ID in column A and return its price from column C.

INDEX works differently: it returns the value at a chosen row and, when needed, column within a range. On its own, it is useful when you already know the position. MATCH complements it by finding the position of a particular value. Together, INDEX and MATCH can find a row by a key and return a value from the corresponding row in a separate range.

These functions do not change the source data. They display a result in the formula cell, so an updated source value will normally be reflected when Excel recalculates the workbook. A lookup is not the same as sorting, filtering, or removing duplicate records.

Build a Basic VLOOKUP

Assume A2:C5 contains a small price list: column A holds IDs 101, 102, 103, and 104; column B holds product names; and column C holds prices 8, 14, 27, and 9.75, respectively. Cell E2 contains the ID 103. With that layout, enter:

=VLOOKUP(E2,A2:C5,3,FALSE)

The result is 27. The first argument is the value to find, the second is the search range, and the third is the column number to return, counted from the left edge of that range. In A2:C5, column A is number 1, B is 2, and C is 3. FALSE requests an exact match, which is generally the safest choice for IDs, names, and other distinct keys.

The lookup value must be in the first column of the selected range. VLOOKUP cannot search column C and return a value from column A using this ordinary arrangement. Also, the column number is relative to the selected range, not the worksheet: if the range begins at B, then B is column 1 within that range.

Use INDEX with MATCH

Keep the same price-list layout, with IDs in A2:A5 and prices in C2:C5, and put the ID 103 in E2. The following formula finds the ID’s position and returns the price at that position:

=INDEX(C2:C5,MATCH(E2,A2:A5,0))

The result is 27. MATCH searches A2:A5 for the value in E2; its final argument, 0, requests an exact match. If the match is in the third cell of that range, MATCH returns 3. INDEX then returns the third value in C2:C5. The ranges need to line up: each ID should correspond to the price on the same worksheet row.

This pairing separates the lookup range from the return range. That lets the search column be to the right or left of the return column, as long as MATCH searches the key range and INDEX returns from the aligned data. It also avoids a hard-coded return-column number, which can be easier to maintain when a table’s layout changes.

Make a Two-Way Lookup

INDEX can select both a row and a column, and MATCH can identify each position. Suppose A2:A4 contains North, South, and West; B1:D1 contains Jan, Feb, and Mar; and B2:D4 contains the corresponding values. In this example, South’s February value is 110. Put South in E2 and Feb in F2. With those labels and values in the stated ranges, use:

=INDEX(B2:D4,MATCH(E2,A2:A4,0),MATCH(F2,B1:D1,0))

The result is 110. The first MATCH finds the row for South, and the second finds the column for Feb. INDEX returns the value at their intersection. This method is useful for reports with changing row and column choices, such as selecting a department and a month from a summary grid.

Check that the row labels and values cover the same number of rows, and that the column headers line up with the data columns. A misspelled label or extra space can prevent an exact match. If users will choose labels from dropdown lists, using the same source labels for the dropdowns and the table can reduce typing mismatches.

Choose the Right Lookup Method

For a quick decision, consider how the data is arranged and how likely its layout is to change:

  • Choose VLOOKUP when the lookup key is in the first column of the range, the return value is to its right, and the table has a stable layout. Its one-function structure is familiar to many spreadsheet users.
  • Choose INDEX with MATCH when the return column may be to the left of the lookup column, when you want separate search and result ranges, or when a column-number argument would be fragile.
  • Choose a two-way INDEX/MATCH when the result depends on both a row label and a column heading, such as a product and a quarter.
  • Consider XLOOKUP if the workbook is used only in a compatible Excel version and a simpler lookup formula would help. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but may not work in older Excel releases.

There is no universal winner. A familiar VLOOKUP can be easier for a small, fixed list, while INDEX and MATCH offer more control over range direction and structure. For shared workbooks, consider which Excel versions recipients use before choosing a newer function.

Exact Match, Approximate Match, and Duplicates

For VLOOKUP, the fourth argument controls the match behavior. FALSE means exact match; TRUE means approximate match. If the argument is omitted, VLOOKUP uses approximate matching. For many everyday lookups, leaving it out is a common source of unexpected results, so state FALSE explicitly when the key should match exactly.

Approximate VLOOKUP has a different purpose: it can find the largest value that does not exceed the lookup value in a sorted first column. This can support banded tables, such as assigning a category based on a threshold. The first column must be sorted in ascending order for reliable approximate results. Do not use approximate matching for unsorted IDs or names.

With exact matching, a duplicate key does not make the formula return every matching record. VLOOKUP returns the first matching row it encounters. MATCH likewise identifies a position rather than producing a list of all matches. If duplicates are possible, check whether the key is truly unique or use a method designed to return multiple results, such as FILTER in Microsoft 365 or Excel 2021 and later.

Common Errors and How to Fix Them

A lookup error usually comes from a mismatch between the lookup value and the source data, a range that excludes the needed cell, or a formula argument that does not describe the intended search. When a formula returns an unexpected value, inspect both the source and the formula rather than immediately changing the match setting.

  • #N/A: The requested value was not found. Check spelling, spaces, number-versus-text differences, and whether the lookup range includes the key. For INDEX/MATCH, confirm that MATCH uses 0 for an exact match.
  • #REF!: A reference may no longer be valid, or VLOOKUP’s column number may exceed the number of columns in its selected range. Recheck the range and return-column number.
  • Wrong result: VLOOKUP may be using approximate matching because its final argument is TRUE or omitted. Specify FALSE for an exact lookup. Also confirm that the intended key appears only once.
  • Numbers stored as text: A displayed value such as 103 may be numeric in one range and text in another. Make the data types consistent; changing cell formatting alone does not always convert the stored value.
  • Unwanted spaces: Imported text can contain leading, trailing, or nonbreaking spaces. Clean the source data or compare the underlying values to find the discrepancy.

If you want a more readable message when an exact INDEX/MATCH search fails, assume the same IDs and prices are in A2:A5 and C2:C5, and E2 contains an ID that is not in the list, such as 999. Use:

=IFNA(INDEX(C2:C5,MATCH(E2,A2:A5,0)),"Not found")

The result is Not found instead of #N/A. IFNA handles the not-found error specifically, so it is preferable to hiding every possible error with a broad error-catching formula when other formula problems should remain visible.

Compatibility and Practical Workbook Design

INDEX, MATCH, and VLOOKUP are established Excel functions and are suitable when a workbook must also be opened in many older Excel versions. XLOOKUP is more recent and is available in Microsoft 365 and Excel 2021 or later; older versions may show an unsupported-function error. When distributing a workbook, check the recipients’ Excel versions if you use functions that are not available to all of them.

For longer lists, format the data as an Excel table using Insert > Table. Tables can expand as new rows are added, and their headers make data easier to understand. Still, verify that the lookup formula refers to the intended columns and that newly added records are included. Keep one clear header row, avoid merged cells in the data area, and use a consistent type and format for keys.

For formulas that will be copied down a column, consider whether the references should move. Relative ranges such as A2:A5 shift when copied; absolute references such as $A$2:$A$5 stay fixed. A worksheet table or deliberately fixed range can help prevent a copied formula from drifting away from the complete lookup list.

Frequently Asked Questions

Is INDEX MATCH better than VLOOKUP?
Not in every situation. INDEX with MATCH can search in either direction and avoids a fixed VLOOKUP column number, while VLOOKUP is often simpler for a stable table with the key on the left and the answer on the right.

Can VLOOKUP return a value to the left?
Not with its standard range-and-column-number setup. The lookup column must be the first column of the selected range, and the result comes from a column to its right. INDEX with MATCH can return from a separate range to the left.

Why does VLOOKUP return the wrong value?
Check whether its final argument is TRUE or omitted, which selects approximate matching, and whether the first column is sorted as required. If you need an exact key match, set the final argument to FALSE and verify that the key is unique.

What does the zero in MATCH mean?
In MATCH’s third argument, 0 requests an exact match. That is the usual setting for looking up an ID, a name, or another value that should match precisely.

Should I learn XLOOKUP instead?
If you use Microsoft 365 or Excel 2021 and later, XLOOKUP is a useful modern option and can make many lookups more direct. INDEX with MATCH remains valuable for compatibility with older workbooks and for understanding how row-and-column lookups work.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.