Formula Errors

Lock Excel Table Column References in INDEX and MATCH Formulas

Tauseeq MagsiTauseeq Magsi Nov 14, 2026 868 views

Question details

Keep Excel table column references fixed when filling INDEX and MATCH formulas to the right.

Lock Excel Table Column References in INDEX and MATCH Formulas
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user is calculating ingredient requirements and production batches with formulas that refer to table columns.
Observed behavior
A structured reference to the Product column can shift to the Quantity column when the formula is filled to the right.
Before you start

Check the table name under Table Design and note the exact column headers used in your formula. The examples below assume a table named Ingredients with Product, Ingredient, and Amount columns.

Solution 1Recommended

Lock structured columns in an INDEX and MATCH formula

Use a single-column range reference to keep each lookup and return column fixed when filling a formula horizontally.

1
Edit the formula

Select the result cell and click the formula bar. Replace a reference such as Ingredients[Product] with Ingredients[[Product]:[Product]]. A plain table-and-column reference alone can still shift when filled right.

2
Lock each table column

For a lookup using product in A2 and ingredient in B1, enter =INDEX(Ingredients[[Amount]:[Amount]],MATCH(1,(Ingredients[[Product]:[Product]]=$A2)*(Ingredients[[Ingredient]:[Ingredient]]=B$1),0)). Adjust the table name, headers, and cells to match your sheet.

3
Fill and verify

Drag the fill handle to the right, then select a filled cell and inspect the formula bar. The table columns should remain fixed; $A2 keeps the product column fixed, while B$1 allows the ingredient column to change.

Lock structured columns in an INDEX and MATCH formula
Multiple matches: MATCH returns the first matching row, not a total. In older Excel versions, confirm this array formula with Ctrl+Shift+Enter.
Calculate with WPS Spreadsheets

Keep lookup ranges fixed in WPS Spreadsheets

Use absolute cell ranges in WPS Spreadsheets to keep INDEX and MATCH lookup columns fixed when filling a formula to the right.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Identify the ranges: Find the product lookup values and the amount values on the sheet. For example, use B2:B100 for products and C2:C100 for amounts.
  3. 3. Enter a locked lookup: Select a result cell and enter =INDEX($C$2:$C$100,MATCH($A2,$B$2:$B$100,0)). Adjust the ranges and lookup cell to fit your workbook.
  4. 4. Fill and check: Drag the fill handle to the right and inspect the copied formula. Expand the fixed ranges if new data is added beyond row 100.
Lock lookup and return ranges with dollar signsOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and fill controlsAccess spreadsheet tools for free
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel table reference change when I fill right?

A reference such as Ingredients[Product] can behave relatively during a horizontal fill. Use Ingredients[[Product]:[Product]] to keep that column fixed.

Can MATCH use more than one criterion?

Yes. Use MATCH(1,(first_range=first_value)*(second_range=second_value),0) to find the first row meeting both conditions. Older Excel versions may require Ctrl+Shift+Enter.

When should I use SUMIFS instead of INDEX and MATCH?

Use SUMIFS when you need to add all Amount values for matching criteria. INDEX and MATCH returns a value from the first matching row.

Do dollar signs lock Excel table columns?

Dollar signs lock regular cell references, such as $A2 or $B$2:$B$100. To lock a structured table column during a horizontal fill, use the repeated-column syntax, such as Ingredients[[Product]:[Product]].