This page explains how to highlight the cell at the intersection of a specific row and column when both must match exactly, a common task in Excel and Google Sheets. You enter a row value and a column value, and the formula returns the exact cell where those two meet, using INDEX and MATCH functions. The calculator below computes the row and column numbers for your lookup values, and the guide shows you how to apply conditional formatting to highlight that cell.
Formula
The core formula for finding the intersection cell is:
INDEX(return_range, MATCH(row_value, row_lookup_array, 0), MATCH(col_value, col_lookup_array, 0))
Where:
- return_range – the range of cells that contains the value you want to return (e.g., A1:Z100).
- row_value – the value you want to match in the row headers (e.g., a product ID).
- row_lookup_array – the range of cells that contains the row headers (e.g., A1:A100).
- col_value – the value you want to match in the column headers (e.g., a date).
- col_lookup_array – the range of cells that contains the column headers (e.g., A1:Z1).
- 0 – tells MATCH to find an exact match.
The MATCH functions return the relative position of the row and column headers. INDEX then returns the value at that row and column position within the return range.
Worked Example
Suppose you have a table where row headers are product IDs (1, 2, 3, …) in column A, and column headers are months (1, 2, 3, …) in row 1. The data occupies B2:E10. You want the value at the intersection of product 3 and month 4.
Using the default values from the calculator: row_value = 3, col_value = 4.
- MATCH(3, A2:A10, 0) returns 2 because product 3 is the 2nd item in the row header list (if A2=1, A3=2, A4=3).
- MATCH(4, B1:E1, 0) returns 3 because month 4 is the 3rd item in the column header list (if B1=1, C1=2, D1=3, E1=4).
- INDEX(B2:E10, 2, 3) returns the value in the 2nd row and 3rd column of that range, which is cell D4 (since B2 is row1 col1, C3 is row2 col2, D4 is row2 col3).
So the intersection value is whatever is in cell D4. In the calculator, the row number and column number are shown as 2 and 3, which are the relative positions used by INDEX.
How to Highlight the Intersection Cell
To highlight the cell itself (not just return its value), use conditional formatting with a formula that checks if the current cell’s row and column match your lookup values.
- Select the entire data range (e.g., B2:E10).
- In Excel: Home → Conditional Formatting → New Rule → Use a formula. In Google Sheets: Format → Conditional formatting → Custom formula is.
- Enter this formula (adjust ranges to match your sheet):
=AND(ROW()=MATCH($A$2,$A$2:$A$10,0)+1, COLUMN()=MATCH($B$1,$B$1:$E$1,0)+1)
Note: The +1 offsets because MATCH returns a relative position, and ROW()/COLUMN() return absolute numbers. If your table starts at row 1 or column 1, adjust accordingly. - Set a fill color and apply.
This formula highlights only the cell where the row header equals the value in A2 and the column header equals the value in B1. Change A2 and B1 to your desired row and column values.
When to Use This Technique
Use this when you need to quickly locate a specific data point in a two-dimensional table, such as:
- Finding a sales figure for a specific product and month.
- Looking up a price for a specific item and size.
- Identifying a score for a student and a test.
- Cross-referencing any two categories in a matrix.
It’s especially useful when the table is large and you want to avoid manual searching. The INDEX/MATCH combination is more flexible than VLOOKUP because it can look left and right, and it doesn’t break if you insert columns.
Common Mistakes and Pitfalls
- Not using 0 for exact match: If you omit the third argument in MATCH, it defaults to 1 (approximate match), which can return the wrong row/column. Always use 0.
- Mismatched range sizes: The row lookup array must be the same length as the return range’s rows, and the column lookup array must match the return range’s columns. Otherwise you get #REF! errors.
- Offset errors in conditional formatting: Forgetting to adjust the +1 offset when your table doesn’t start at row 1/column 1 will highlight the wrong cell.
- Text vs. number mismatch: If your lookup value is a number but the header is text (or vice versa), MATCH won’t find it. Ensure data types match.
- Leading/trailing spaces: Extra spaces in headers cause match failures. Use TRIM() if needed.
Alternatives to INDEX/MATCH
If you’re using Excel 365 or Excel 2021, you can use XLOOKUP for a simpler approach:
=XLOOKUP(row_value, row_lookup_array, XLOOKUP(col_value, col_lookup_array, return_range))
This nested XLOOKUP works similarly: the inner XLOOKUP returns the entire column for the matched column header, and the outer XLOOKUP finds the row within that column. It’s easier to read and doesn’t require the 0 argument (exact match is default).
Another alternative is using SUMPRODUCT for numeric data: =SUMPRODUCT((row_range=row_value)*(col_range=col_value)*return_range). This returns the sum of all matching intersections, which works only if there’s exactly one match and the values are numeric.
Frequently Asked Questions
What if my row and column headers are not numbers but text?
The same formula works with text. Just replace the numeric values with text strings (e.g., “Product A”, “Jan”). Make sure the text matches exactly, including case and spaces. MATCH is case-insensitive by default, but spaces matter.
Why does my formula return #N/A?
#N/A means MATCH couldn’t find the value. Check that the lookup value exists in the header range, that there are no extra spaces, and that the data types match (number vs text). Also ensure the lookup array is a single row or column, not a two-dimensional range.
Can I highlight the entire row and column instead of just the intersection?
Yes. Use two separate conditional formatting rules: one for the row (e.g., =ROW()=MATCH(…)) and one for the column. Or use a formula that checks either condition: =OR(ROW()=…, COLUMN()=…). This highlights the whole row and column, making the intersection obvious.
Does this work in Google Sheets?
Yes, INDEX, MATCH, and conditional formatting all work in Google Sheets. The formulas are identical. The only difference is the menu path for conditional formatting, but the custom formula field works the same.
How do I make the highlight dynamic so it changes when I update the lookup values?
Simply reference a cell (like A2 for row value and B1 for column value) in your conditional formatting formula. When you change those cells, the highlight moves automatically. No need to reapply the rule.