Understand Excel INDEX With Practical Lookup Examples

Excel’s INDEX function returns the value at a specified position in a range. Give it a range, a row number and, when needed, a column number; Excel retrieves the value where that relative row and column meet. Its basic syntax is =INDEX(array, row_num, [column_num]).

Choose a relative row and column to see which value INDEX returns.

INDEX can return either a value or a cell reference. When both row and column numbers are supplied, the result comes from their intersection. The row and column numbers are counted within the range passed to INDEX, not from the edges of the worksheet (Microsoft Support).

A Simple INDEX Formula Returns One Position

Suppose this dataset occupies A1:D5:

Product	Region	Units	Revenue
P-101	North	120	3600
P-102	West	95	3325
P-103	South	140	4200
P-104	East	110	3850

The formula =INDEX(A2:D5,3,4) returns 4200.

The first argument, A2:D5, defines the array that Excel can search. It contains four rows and four columns. The second argument selects row 3 within that array, which is the record for P-103. The third argument selects column 4, which is Revenue. Their intersection contains 4200.

The formula does not treat worksheet row 4 as row 4. Although P-103 appears on worksheet row 4, it is the third data row within A2:D5. Changing the start of the array changes the relative coordinates.

For example, if the array included the headings in row 1, the same record would occupy a different relative row. Formula arguments therefore need to match the exact boundaries of the selected range. This distinction is central to understanding what INDEX does: it retrieves by position inside its array, not by the worksheet’s visible row and column labels.

One-Column INDEX Formulas Need Only a Row Number

When the array contains one column, the row number is enough. The formula =INDEX(D2:D5,3) also returns 4200 because 4200 is the third item in D2:D5.

In this version, Excel does not need a column argument because the range has only one column. The three parts are:

1. D2:D5 is the return range.
2. 3 is the relative position inside that range.
3. 4200 is the value stored at that position.

This shorter form is often useful when another part of a formula will calculate the row number. Instead of asking INDEX to identify a product or other label, the formula gives it a numeric position and asks it to retrieve the corresponding result.

A one-row range works on the same positional principle, but the relevant movement is across columns. In either orientation, the supplied position must remain inside the selected array.

INDEX Selects a Position While MATCH Finds It

On its own, INDEX is most direct when the required row and column numbers are already known. In a lookup, however, users usually know an identifier such as a product code rather than its current row number. MATCH can calculate that position for INDEX.

If cell G2 contains P-103, use =INDEX($D$2:$D$5,MATCH(G2,$A$2:$A$5,0)) to return its revenue.

The inner formula, MATCH(G2,$A$2:$A$5,0), searches A2:A5 for the value in G2. For P-103, it returns 3 because that code is the third item in the lookup range. INDEX then evaluates as though the formula were =INDEX($D$2:$D$5,3), returning the third value in D2:D5: 4200.

The final 0 tells MATCH to request the first exact match. This exact-match search does not require the lookup range to be sorted. MATCH returns a relative position rather than the matched value, which is why its result can serve as the row argument for INDEX (Microsoft Support).

The dollar signs make the lookup and return ranges absolute references. If the formula is copied elsewhere, $A$2:$A$5 and $D$2:$D$5 remain fixed. The reference to G2 remains relative because a copied formula may need to read a different lookup cell.

Lookup and Return Ranges Must Stay Aligned

An INDEX and MATCH lookup depends on positional alignment. In the example, A2:A5 and D2:D5 each represent the same four records in the same order:

Position	Lookup Range	Return Range
1	P-101	3600
2	P-102	3325
3	P-103	4200
4	P-104	3850

MATCH finds a position in the lookup range, and INDEX retrieves that same position from the return range. It does not verify that both ranges describe the same records.

If the lookup range starts at A2 but the return range starts at D3, position 3 refers to different worksheet rows. The formula may still return a valid number, but it will belong to the wrong product. This is more difficult to detect than an explicit error because the result can look plausible.

Sorting only one of the two ranges causes the same problem. The identifiers and results no longer occupy corresponding positions. Keep every record in one complete row, select the whole dataset when sorting, and make the lookup and return ranges start and end on corresponding records.

Duplicate lookup values also require attention. With the exact-match formula shown above, MATCH returns the position of the first matching product code. INDEX therefore returns the revenue associated with that first occurrence. The formula does not combine duplicate records or decide which duplicate is authoritative.

Two MATCH Functions Create a Row-and-Column Lookup

INDEX can also retrieve a value from a matrix when both the row label and column heading vary. Suppose product codes occupy A2:A4, month headings occupy B1:D1, and the values occupy B2:D4. If G2 contains a product and G3 contains a month, use =INDEX($B$2:$D$4,MATCH(G2,$A$2:$A$4,0),MATCH(G3,$B$1:$D$1,0)).

The first MATCH searches the product labels and supplies the relative row number. The second searches the month headings and supplies the relative column number. INDEX returns the value at the intersection of those two positions.

The three ranges have distinct jobs:

• $B$2:$D$4 contains the values that can be returned.
• $A$2:$A$4 contains labels aligned with the rows of that value range.
• $B$1:$D$1 contains headings aligned with its columns.

The value range excludes both sets of labels. This keeps the row and column positions returned by MATCH aligned with the data matrix supplied to INDEX.

An INDEX and MATCH lookup can also return a value from a column to the left of the lookup column. Unlike VLOOKUP, its lookup column does not have to be positioned to the left of its return column (Microsoft Support).

Common INDEX Errors Point to Different Problems

A #REF! error means the requested row or column falls outside the selected range. For example, =INDEX(A2:D5,5,2) asks for a fifth row in an array that contains only four rows. The formula cannot resolve that position. Check the array boundaries and the row or column number being supplied.

A #N/A error in an INDEX and MATCH formula usually comes from MATCH, not from the final retrieval step. It means the lookup value was not found under the requested matching rules. Check the lookup value, the lookup range and whether the formula requests an exact match with 0.

A wrong but believable value often indicates misaligned ranges. The formula can calculate normally even when its lookup and return ranges begin on different rows. Compare the first and last record represented by each range rather than checking only whether both ranges contain the same number of cells.

An omitted 0 in the MATCH portion can also change the matching behavior. The example formulas deliberately include 0 because they are looking for exact product codes or headings.

If a result changes after editing or sorting the data, inspect whether the operation affected the entire dataset. Moving or sorting only the lookup range breaks its positional relationship with the return range. Absolute references prevent ranges from shifting when a formula is copied, but they do not prevent users from rearranging only part of a dataset.

Blank cells require interpretation rather than an automatic correction. If MATCH finds a valid record and the corresponding return cell is blank, the lookup position may be correct even though the source record lacks a value. That is different from #N/A, where the requested identifier was not found.

XLOOKUP May Be Clearer for a Straightforward Lookup

If a workbook only needs a direct lookup, XLOOKUP may be easier to read because it accepts separate lookup and return ranges and defaults to exact matching. It is not available in Excel 2016 or Excel 2019, however (Microsoft Support).

INDEX with MATCH remains useful for workbooks that need compatibility with those older versions. It also expresses two-dimensional lookups clearly: one MATCH determines the row, another determines the column, and INDEX returns their intersection.

The choice is therefore tied to the workbook’s requirements. A single modern exact lookup may be simpler with XLOOKUP. A workbook intended for Excel 2016 or Excel 2019 cannot rely on that function, while an INDEX and MATCH construction can support the lookup pattern described above.

Check INDEX Results Before Publishing Them

Treat the formula workbook as the working source and inspect its calculated output before sharing it. Check missing identifiers, duplicate keys, #N/A results, #REF! errors and values that appear inconsistent with their records. A formula that calculates successfully can still return the wrong record if its ranges are misaligned.

Publish only the fields readers need. The source workbook may contain lookup columns, helper ranges or sensitive fields that are not necessary in the shared result. A harmless-looking output formula does not make the underlying source data safe to distribute.

If the output will be exported, account for the destination format. CSV does not preserve workbook formulas or Excel-specific structure. It stores a flat representation of the exported data instead. Choose deliberately between a calculation-ready workbook and a flat data file using this CSV versus XLSX comparison.

Before export, decide whether readers need the formulas or only their calculated values. Then verify the published rows independently of the working formula, especially when the lookup source contains duplicate identifiers or blank results.