Lock Excel Table Column References in INDEX and MATCH Formulas
Question details
Keep Excel table column references fixed when filling INDEX and MATCH formulas to the right.

- 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.
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.
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.
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.
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.
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.

Sum matching ingredient amounts with SUMIFS
Use SUMIFS instead of INDEX and MATCH when several rows match the same product and ingredient and their amounts should be added.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 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. 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. 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.

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]].




