XcelFormula
Sheets

B2=

Guides

Master INDEX for Advanced Data Retrieval in Excel and Google Sheets

Use INDEX to return a value or reference from a range by specifying its row and column positions. Pair it with MATCH to find a position from a lookup…

Published 4 November 2026 · 6 min read

In this piece 8
  1. Key facts
  2. INDEX returns a value by position
  3. MATCH makes INDEX searches flexible
  4. Ranges and spreadsheet choices affect the design
  5. What this means for you?
  6. What to do now?
  7. Sources
  8. About this page

By · Published

Use INDEX to return a value or reference from a range by specifying its row and column positions. Pair it with MATCH to find a position from a lookup value, then retrieve the related result. This approach supports flexible lookups, including returns from columns to the left, and can build multi-field results. (Support.microsoft.com)

Key facts

  • With both row and column arguments, INDEX returns the value where those positions meet in the range.
  • Microsoft’s example pairs INDEX with MATCH, using MATCH’s result as an argument to INDEX.
  • Google Sheets supports cell formulas typically found in desktop spreadsheet packages.

INDEX returns a value by position

INDEX works from positions in a range rather than searching for a value itself. Give it a range, a row position and a column position; it returns the value at their intersection. This makes it useful when the row and column are already known or calculated.

For a table with several rows and columns, the row argument identifies a position within the range, while the column argument does the same across its columns. The returned cell is the point where those positions meet.

INDEX can also return a reference rather than only a displayed value. Microsoft notes that other formulas can interpret that result as a reference; its example describes CELL using INDEX’s return value as a cell reference.

When one position argument is zero, INDEX can return the whole corresponding row or column. Depending on the form used, the result is described as a reference or an array. That behaviour can help when a later formula needs a complete slice of a range rather than one cell.

Keep the range and its positions aligned. A result can be valid but refer to an unintended cell if the range changes while the row or column calculation does not. Use clear range references and test the returned result against the source table.

MATCH makes INDEX searches flexible

To retrieve a value from a table when you know a label rather than its position, combine INDEX with MATCH. MATCH finds a position, and INDEX uses that result as an argument to return the associated value. Microsoft presents this pairing as an alternative to using VLOOKUP.

This separation is useful when the lookup field and the result field are not arranged in a convenient order. The position can be found in one range, while INDEX returns from another. Microsoft’s example describes using INDEX and MATCH to find a city and return its population from a specified column.

For a practical formula, make the MATCH range contain the labels you want to search. Use its result for INDEX’s row position, and set the INDEX range to the values you need returned. Check that both ranges begin at corresponding rows, or the position may point to the wrong record.

The same method can return more than one related field. Microsoft describes a formula that uses INDEX and MATCH twice: first to return an invoice number, then to return its date. Its example also shows returning an invoice number and date for each of five cities.

Excel also offers XLOOKUP, which Microsoft describes as working in any direction and returning exact matches by default. INDEX with MATCH remains a useful option when you want to calculate a position separately or work with existing formulas built around that pattern.

Ranges and spreadsheet choices affect the design

Choose the INDEX form to match the shape of the source data. When both row and column positions are supplied, INDEX returns their intersecting value. When a position is zero, it can return an entire row or column, which changes what a formula receives.

If the source ranges sit on different sheets, Microsoft recommends the array form of INDEX and using another function to calculate the range that makes up the array. This gives the formula a way to work with separated ranges without treating them as one simple block.

For either tool, make the formula’s purpose easy to inspect. Keep the lookup range, return range and result position easy to identify. If a formula will be copied or reused, test it with labels near the top, middle and end of the data. These checks are practical safeguards, not a substitute for verifying the source range.

Microsoft’s function reference provides detailed information for each function through its name in the first column. Use the relevant function entry when you need to confirm a specific argument or behaviour before changing a formula.

What this means for you?

If you are a person maintaining a stock list, customer register or project table, INDEX with MATCH can separate the search from the result. You can search a label in one range and return a related value from another. That design is especially useful when the return field sits to the left of the lookup field, or when you want the formula’s search and return ranges to remain visible.

Start by deciding what the formula should return. Then identify the field that contains the value you will search for, and make sure the two ranges cover corresponding records. This simple planning step helps avoid formulas that return a plausible value from the wrong row.

If you need several outputs for the same match, build a separate INDEX result for each return field, using the same matched position where appropriate. Microsoft’s example uses the pairing twice to return an invoice number and its date. Keep each formula readable so a future editor can see which field it returns.

When your formula needs a full row or column, consider whether a zero position argument is appropriate. INDEX can return an array or reference for that whole row or column, but this differs from retrieving a single intersection. Make the intended output clear before using the result in another formula.

In Google Sheets, the same general formula-based workflow fits a tool that supports common spreadsheet cell formulas.

What to do now?

  1. Choose the value you want returned, then identify the range containing it. Keep the intended output separate from the lookup field.
  2. Set the MATCH range to the labels you will search. Set INDEX to the corresponding return range, and use MATCH’s result as INDEX’s row position.
  3. Check that the ranges cover the same records in the same order. Try the formula with a label whose expected result you can verify directly.
  4. Add a separate INDEX result for each additional field you need. Reuse the matched position where it refers to the same record.
  5. Use a zero position only when you intend to return a whole row or column, rather than one intersecting cell.
  6. Review the relevant function reference if you are unsure about an argument, then test the finished formula in the spreadsheet where it will be used.

Sources

About this page

Written with AI assistance from the sources listed above, then checked by automated fact, source, repetition and quality checks before publishing.

Facts and links checked on 4 November 2026. Summarises the sources linked above; check the linked source for the latest version.

How we researched this: we compiled this guide from the official and independent sources listed above, and checked the facts on the date shown. Our editorial policy explains how we choose and check sources.

Related articles