Use Named Ranges to Retrieve a Value by Row and Column in WPS Office Spreadsheets
Question details
The user wants to retrieve the value at the intersection of a named row and named column in a spreadsheet, such as the February value for Bananas, without using VLOOKUP.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet contains row and column labels that have been defined as named ranges, and the user needs a formula to return the intersecting cell value.
- Observed behavior
- The user needs a reliable formula instead of VLOOKUP, while avoiding named ranges that conflict with A1-style cell references.
Confirm that the row and column labels are already defined as named ranges in WPS Office Spreadsheets. Avoid names that conflict with cell references, such as A1 or R1C1, because these can make the intersection formula return unexpected results.
Retrieve the Intersection of Named Ranges with the Space Operator
Use this method when the row and column names are already defined and you want the intersecting cell value directly.
The space operator returns the common area of two ranges. If the named row Bananas and the named column Feb overlap in one cell, =Bananas Feb returns that cell's value.
Click the empty cell where you want the intersection value to appear.
Type =Bananas Feb or =(Bananas Feb), then press Enter. For example, =Bananas Feb returns the February value for the Bananas row.
Check that the returned value corresponds to the cell where the Bananas row and Feb column meet.
Use INDEX and MATCH for Row and Column Lookups
Use this formula when named ranges are not configured or when you need a flexible lookup based on label text.
Use Named Range Intersections in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same named range intersection and INDEX/MATCH formulas as Excel, so you can retrieve the value where a named row and named column meet without VLOOKUP.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the file that contains the row and column labels.
- 2. Define row and column names: Select a row label range, go to Formulas > Name Manager > New, enter a name such as Bananas, and repeat for the column label Feb.
- 3. Enter the intersection formula: In an empty cell, type =Bananas Feb or =(Bananas Feb) and press Enter to retrieve the intersecting value.
- 4. Review and adjust names: If the result is unexpected, open Formulas > Name Manager to verify the ranges and rename any names that conflict with cell references.
Frequently Asked Questions
How do I return the intersection of a named row and a named column in WPS Office?
Enter the row name and column name separated by a space, for example =Bananas Feb, then press Enter. WPS Office Spreadsheets will return the value at the intersecting cell.
Can the intersection formula be used inside SUM?
Yes. Use formulas such as =SUM(Bananas Feb), =SUM(Apples:Bananas Feb), or =SUM(Apples Feb,Oranges Feb) to sum the intersecting cells or ranges.
Why does =Bananas Feb return #NULL!?
#NULL! usually means the two named ranges do not intersect. Check the ranges in Formulas > Name Manager and make sure the row and column names share the intended cell.
Can I use INDEX and MATCH instead of named ranges?
Yes. In a cell, enter =INDEX(C3:C5,MATCH("Bananas",A3:A5,0)). Adjust the first range to the value column and the second range to the row label column.
What should I avoid when creating names for intersection formulas?
Avoid names that conflict with cell references such as A1 or R1C1. Use Formulas > Name Manager to create and review names.
