Yes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you copy the formula. Add dollar signs to the cell references you want to keep fixed, such as $B$2:$B$20; INDEX does not make references absolute automatically.
What an absolute reference means in Excel
A cell reference has a column and a row. In a relative reference such as B2, both can change when you copy a formula to another cell. An absolute reference, $B$2, locks both the column and row. Copying a formula elsewhere will leave that reference pointing to B2.
Excel also supports mixed references. $B2 locks the column but allows the row to change; B$2 locks the row but allows the column to change. These distinctions matter when a formula will be filled down, across, or in both directions. The dollar signs affect how a reference adjusts when copied; they do not prevent the referenced cell’s value from changing.
To change a reference while editing a formula in Excel for Microsoft 365 on Windows, put the cursor on the reference and press F4. Repeated presses cycle through relative, absolute, and mixed forms. On a Mac, Command+T cycles reference types in Excel; keyboard settings may affect which key combination is available. You can also type the dollar signs yourself.
How INDEX uses cell references
INDEX returns an item from a range based on its position. In its common array form, the first argument is the range to search, followed by a row number and, when needed, a column number. The position is counted within the supplied range, not from worksheet row 1. INDEX can use relative, absolute, or mixed references just like other Excel functions.
For example, with the values 10, 20, 30, and 40 in B2:B5, this formula returns 30:
With those values in B2:B5, the formula is =INDEX($B$2:$B$5,3). Its result is 30, the third item in the specified range, which is in worksheet cell B4.
That example uses a locked range, but the dollar signs are not required for the formula to work once. They matter if you copy the formula and want its range to stay put. INDEX also has a reference form that can work with multiple areas, but most everyday lookups use the array form with one contiguous range.
Why lock INDEX ranges before copying
Suppose a formula looks up a product code in a fixed list and returns the matching price. If you copy the formula down and its lookup ranges are relative, Excel may shift those ranges one row at a time. The first result can be correct while later rows search the wrong part of the list. Absolute references keep the lookup and return ranges stable.
In a typical INDEX-and-MATCH lookup, you often lock the data ranges but leave the lookup value relative. That lets each copied formula use the next row’s lookup value while continuing to search the same list. Whether every reference should be absolute depends on where you plan to copy the formula; locking everything indiscriminately can make a formula less flexible.
Build and copy an INDEX formula
For a one-column lookup, first identify the lookup range, the range containing the results, and the cell that holds the value to find. MATCH finds the value’s position; INDEX uses that position to return the corresponding item. The two ranges should have the same number of rows and be aligned item by item.
With IDs in A2:A4 (101, 102, 103), prices in B2:B4 (12, 18, 25), and the ID to find in D2 (102), enter this formula in E2: =INDEX($B$2:$B$4,MATCH(D2,$A$2:$A$4,0)). The result is 18. The zero requests an exact match, and copying the formula down changes D2 to D3 while keeping both lookup ranges fixed.
To fill the formula down, select the cell and drag its fill handle, or copy and paste it into the cells below. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. Check a copied formula if the results look wrong: a relative lookup cell should normally advance, while fixed lookup ranges should remain unchanged.
Use mixed references for a two-way lookup
A two-way lookup finds a row and a column, such as a person’s name and a month, then returns the value at their intersection. This is a useful case for mixed references because the formula may be copied both down and across. Locking both data ranges keeps the table boundaries fixed; mixed references let the row label and column heading adjust in the intended direction.
Assume names are in A2:A4, month headings are in B1:D1, and the values are in B2:D4. The rows contain Ava (120, 150, 180), Ben (100, 130, 160), and Cam (90, 110, 140), in that month order. Put Ben in F2 and Mar in G1. In G2, use =INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),MATCH(G$1,$B$1:$D$1,0)). The result is 160, at the intersection of Ben’s row and the Mar column.
Here, $F2 fixes the lookup column while allowing the row to change when copied down. G$1 fixes the heading row while allowing the column to change when copied across. The ranges have dollar signs in both dimensions because the table and label lists should stay in place. This arrangement is a practical example of why an INDEX formula does not always need every reference locked in the same way.
Common reference mistakes and how to fix them
- The range shifts after copying: Check whether a range such as the INDEX result range or MATCH lookup range should be absolute. Add dollar signs to the row and column that must remain fixed.
- The lookup cell never advances: If each row should search for a different value, make sure the lookup-value reference is not fully absolute. A reference such as
D2can change as the formula is filled down. - The result is from the wrong position: INDEX counts positions within its range. If the range starts at B2, row number 1 means B2, not worksheet row 1. Confirm the ranges are aligned and the MATCH result represents the intended position.
- The formula returns an error: MATCH with an exact-match argument returns an error if it cannot find the value. Check for extra spaces, mismatched data types such as text versus numbers, and a lookup range that does not include the item. An invalid INDEX position can also produce a reference error.
- Dollar signs seem to change the answer: They change how a reference behaves when copied; they do not convert a lookup into an exact match or repair mismatched ranges. The MATCH argument and the data still determine what is found.
When troubleshooting, select a copied formula and inspect its references in the formula bar. Compare it with the original. A quick way to diagnose a shifted range is to click each referenced range in the formula and confirm that it still covers the intended list or table.
INDEX references compared with other approaches
INDEX is useful when the return range and lookup range are separate, when a formula needs to return a value from a row-and-column intersection, or when you want to combine it with MATCH. Absolute references are not unique to INDEX: the same dollar-sign rules apply to SUM, AVERAGE, and other formulas.
For a straightforward lookup in Microsoft 365, XLOOKUP may be easier to read because it directly takes a lookup range and a return range. It is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019. INDEX and MATCH remain useful in older workbooks and for two-dimensional lookups. Whichever method you choose, copying formulas still requires you to decide which references should move.
Excel tables offer another option for maintaining data ranges. When a data range is formatted as a table, structured references can make formulas easier to understand and generally expand with the table as rows are added. They use table and column names rather than ordinary A1-style references, so dollar signs are not used in the same way. For a small fixed range, conventional absolute references are often simpler.
Frequently Asked Questions
Does INDEX make a reference absolute automatically?
No. INDEX uses the references you provide. Add dollar signs to lock a row, a column, or both if you need those parts to stay fixed when copying the formula.
Should I use absolute references in INDEX and MATCH?
Usually, lock the lookup and return ranges when copying a lookup formula down or across. Keep the lookup-value reference relative if each copied formula should use a different value. Use mixed references when copying in two directions requires one part of a reference to change and the other to stay fixed.
What does the dollar sign do in an INDEX formula?
It controls how a cell or range reference adjusts when a formula is copied. In $A$2:$A$10, neither the column nor the row shifts; in A$2, the row stays fixed but the column can change.
How do I lock an INDEX reference quickly?
While editing the formula, place the cursor on the cell reference and press F4 on Windows, or Command+T in Excel for Mac, to cycle through reference styles. You can also add dollar signs by typing them directly.
Can an INDEX formula work without dollar signs?
Yes. Relative references are valid and often useful when a formula is meant to adjust as it is copied. Dollar signs are a choice about copying behavior, not a requirement for INDEX to calculate.
Related
- Python pandas Excel Formatting: A Practical Guide to Styles
- Correlation Table in Excel: Create and Read One
- Python Pandas Excel Export: Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Right-Click Shortcut: Open the Context MenuIn Excel for Microsoft 365 on Windows, select a cell or range and press Shift+F10 to open its…
- SUMIF in Excel: Match Cells That Contain Specific TextYes. SUMIF can add values when a cell contains particular text: with descriptions in A2:A7 and amounts in…
- How to Count Unique Values in an Excel PivotTableTo count distinct values in an Excel PivotTable, create the PivotTable with Add this data to the Data…
- MATCH Excel String: Find Exact Text MatchesTo find an exact text string in Excel, use MATCH with its third argument set to 0. With…
- Excel LOWER Formula: Convert Text to LowercaseUse Excel’s LOWER function to convert text to lowercase: if the original text is in A2, enter =LOWER(A2)…
- Excel Character Count: Use the LEN FormulaTo count characters in an Excel cell, use the LEN function: if the text is in A2, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.