Function Problems

How to Return the Cell Two Columns Left of the Minimum Value in Excel

Khadija KhanKhadija Khan Nov 13, 2026 869 views

Question details

The user needs an Excel formula to find the minimum price in a row and return the store name located two columns to the left of that minimum without receiving #N/A errors.

How to Return the Cell Two Columns Left of the Minimum Value in Excel
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
A row contains store names and price values. The minimum price is located with MIN, and the corresponding store name two columns to the left must be returned.
Observed behavior
INDEX and MATCH ranges are not aligned with the left offset, so the formula can return #N/A or the wrong cell.
Before you start

Confirm that the result column is exactly two columns to the left of the first price column. If the price range starts in C, the result column should start in A; if this layout is different, count the actual left offset first.

Solution 1Recommended

Fix the #N/A by aligning the MATCH range before subtracting 2

Use this when you want to keep the full row as the INDEX range and subtract the two-column offset from a MATCH range that starts at the same left edge.

The #N/A typically occurs because MATCH starts at C2 while INDEX starts at A2, so subtracting 2 from a C2-relative match position can produce a negative position. Align the MATCH lookup to A2:K2 and then subtract 2.

1
Select the result cell

Click the empty cell where you want the store name to appear, for example B9.

2
Enter the aligned INDEX/MATCH formula

Type =INDEX(A2:Z2,1,MATCH(MIN(C2:K2),A2:K2,0)-2) and press Enter. In this formula, A2:Z2 is the row to index, C2:K2 is the price range, and A2:K2 is the aligned MATCH lookup.

3
Adjust the column references

Replace C2:K2 with your actual price range and make sure two columns exist to the left of that range within the INDEX row.

4
Confirm with a different minimum

Change a price so a new column becomes the minimum, then verify that the returned store changes accordingly.

Fix the #N/A by aligning the MATCH range before subtracting 2
Aligned ranges prevent #N/A: Because MATCH returns positions within A2:K2, subtracting 2 gives valid positions from 1 to 9 for prices in C2:K2.
WPS Spreadsheets formula solution

Return the Cell Two Columns Left of the Minimum Price in WPS Spreadsheets

Use WPS Spreadsheets' INDEX, MATCH, and MIN functions to locate the minimum price in a row and return the store name from two columns to the left without #N/A errors.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office Spreadsheets and open the file containing the row of prices and store names.
  2. 2. Select the result cell: Click the cell where you want the store name to be displayed, for example B9.
  3. 3. Enter the corrected INDEX/MATCH formula: Type =INDEX(A2:Z2,1,MATCH(MIN(C2:K2),A2:K2,0)-2) and press Enter. Adjust A2:Z2 and C2:K2 to your actual worksheet columns.
  4. 4. Test the result: Change one of the prices to make another cell the minimum, and confirm that the formula returns the store two columns to the left.
Fixes #N/A by aligning INDEX and MATCH ranges before applying the offsetWorks with standard Excel lookup functions, so your existing workbook logic remains validOpens and saves .xlsx, .xls, and .csv files with formatting and formula compatibilityFree, lightweight, and easy-to-use spreadsheet app for everyday Excel tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does =INDEX(A2:Z2,1,MATCH(MIN(C2:K2),C2:K2,0)-2) return #N/A?

Because the MATCH range starts at C2, so a minimum in C2 gives MATCH position 1 and INDEX position -1. Use an aligned MATCH range: =INDEX(A2:Z2,1,MATCH(MIN(C2:K2),A2:K2,0)-2), noting that the lookup range is A2:K2, or use the direct form =INDEX(A2:K2,1,MATCH(MIN(C2:K2),C2:K2,0)).

Can I use the same approach if the price range starts in a different column?

Yes. Start the INDEX range at the column you want to return and keep it aligned with the MATCH range at the price start. For example, if prices are in D2:M2 and the result is two columns left, use =INDEX(B2:M2,1,MATCH(MIN(D2:M2),D2:M2,0)).

What happens if there are duplicate minimum values in the row?

MATCH with 0 as the third argument returns the first exact match from left to right. The formula returns the cell two columns left of that first minimum.

Will this formula work when copied down multiple rows?

Yes. Change the row number from 2 to the current row and let the formula copy down normally. For example, =INDEX(A2:Z2,1,MATCH(MIN(C2:K2),A2:K2,0)-2) shifts to row 3, row 4, and so on when copied down.