Find the First Match in a WPS Spreadsheet Row and Return a Value Three Columns Right
Question details
Find the first occurrence of a specified value in a worksheet row and return the value located three columns to the right.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to search a row for the first exact match, such as "x", and retrieve a related value at a fixed column offset.
- Observed behavior
- The desired result is the cell value three columns to the right of the first matching cell in the selected row.
Confirm that the lookup value and the values in the target row use the same format, such as text rather than mixed text and numbers. Adjust the row reference in the formula to match the row that contains your data.
Use INDEX and MATCH to return the value three columns after the first match
Use this formula when you need the first exact match in one row and the related value exactly three columns to its right.
MATCH returns the position of the first exact occurrence of the lookup value in the row. Adding 3 moves that position three columns right, and INDEX returns the value from that location.
Click the cell where you want WPS Spreadsheets to display the returned value.
Type =INDEX(3:3,MATCH("x",3:3,0)+3) and press Enter. Replace "x" with your lookup value if needed.
Replace both instances of 3:3 with the row you want to search. For example, use =INDEX(A3:AM3,MATCH("x",A3:AM3,0)+3) to limit the search to columns A through AM in row 3.
If the first x is in column AJ, the formula returns the value in column AM, which is three columns to the right.
Look Up the First Row Match with WPS Spreadsheets
WPS Spreadsheets supports INDEX and MATCH formulas for finding the first exact value in a row and returning a related value at a specified column offset. Enter the formula directly in a result cell and update the lookup value or range as your worksheet changes.
- 1. Open the worksheet in WPS Spreadsheets: Open your XLS, XLSX, or WPS spreadsheet file and locate the row containing the lookup values.
- 2. Choose an output cell: Click an empty cell where the returned result should appear.
- 3. Enter the lookup formula: Enter =INDEX(3:3,MATCH("x",3:3,0)+3), then replace the row number and lookup value as needed.
- 4. Press Enter to calculate: Press Enter to return the value located three columns after the first exact match.
Frequently Asked Questions
How do I return a value from a specific number of columns to the right of a match?
Use INDEX with MATCH and add the number of columns to the MATCH result. For a three-column offset, use =INDEX(3:3,MATCH("x",3:3,0)+3).
Does MATCH return the first occurrence when a row contains multiple matches?
Yes. With the third MATCH argument set to 0, MATCH finds the first exact occurrence from left to right in the selected range.
Why does the INDEX and MATCH formula return #N/A?
#N/A usually means the lookup value was not found in the referenced row or range. Check spelling, spaces, and whether the value is stored as text or a number.
Can I use a cell reference instead of typing x in the formula?
Yes. If the lookup value is in B1, use =INDEX(3:3,MATCH(B1,3:3,0)+3). This lets the result update when you change B1.
