How to use two-way lookup formula in Excel?
A two-way lookup allows you to retrieve the value residing at the intersection of a specific row and column within a table. This technique is valuable when your dataset has distinct row labels and column headers, and you need to pinpoint a particular value based on these criteria. For example, imagine managing a sales report, attendance sheet, or budget table where you wish to quickly identify the value corresponding to a certain date and an employee’s identifier. With Excel’s two-way lookup functionality, you can efficiently extract this information. The screenshot below demonstrates a typical scenario: the value at the intersection of row "AA-3" and column "5-Jan" is returned.
Two-way lookup with formulas
Performing a two-way lookup in Excel is a straightforward approach for obtaining a value at the intersection of specified row and column headers, especially when dealing with well-structured tables. The two-way lookup is applicable across many scenarios, such as comparing employee records by date, extracting budget figures based on region and month, or locating test scores for a particular student and subject.
While formulas are flexible and convenient, their main limitation is that they require the table structure to remain fixed. For more dynamic or automated needs, other solutions may be preferable—additional methods are described below.
To run a two-way lookup using formulas, begin with these steps:
1. List the column headers and row headers you plan to search for. Keeping your headers accurate and consistent helps avoid lookup errors caused by extra spaces or inconsistent formats. Here’s an example of a well-labeled table:
2. In the cell where you wish to display the result, enter one of the following formulas depending on your table’s layout:
Formula 1: INDEX and MATCH Combination
=INDEX(A1:I8,MATCH(L1,A1:A8,0),MATCH(L2,A1:I1,0))This formula locates the row and column indices by matching the specified headers, then returns the value at their intersection.
Formula 2: SUMPRODUCT for Numeric Tables
=SUMPRODUCT((A1:A8=L1)*(A1:I1=L2),A1:I8)SUMPRODUCT works best when your data contains only numeric values and may not return the expected outcome for text results.
Formula 3: VLOOKUP with MATCH
=VLOOKUP(L1,$A$1:$I$8,MATCH(L2,B1:I1,0)+1,FALSE)This method effectively looks up the row first, then uses MATCH to determine the column offset.
Tips：
(1) Parameter explanation:
A1:A8is the range for row labels,L1is the specific row label you want to find;A1:I1is the range for column headers,L2is the target column header;A1:I8is the entire table range; adjust these references as needed to fit your data.
(2) If your lookup values are text and you use SUMPRODUCT, it will return 0. In such cases, it is recommended to use the INDEX/MATCH combination.
When entering formulas, ensure that your header values in L1 (for rows) and L2 (for columns) match exactly with those in your table, including case sensitivity if required.
3. Press the Enter key to confirm the formula. The cell you selected will now display the value at the intersection of your specified row label and column header.
Precautions and troubleshooting:
- If the formula returns an error such as #N/A, double-check that your headers do not contain extra spaces or mismatched capitalization.
- Copying formulas across cells may require changing from relative to absolute references; use $ symbols as needed.
- If your table size is large or variable, consider dynamic named ranges or alternative solutions like VBA below for better scalability.
VBA macro for two-way lookup
In situations where formula-based two-way lookup becomes restrictive—such as needing case-insensitive searches, supporting dynamic range sizes, or automating repeated lookups—a custom VBA macro can be a practical solution. VBA is especially valuable for users who frequently work with changing table structures or require lookups integrated into automated workflows.
Here’s how to set up and use a VBA macro for two-way lookup in Excel:
1. Go to Developer Tools > Visual Basic, which opens the Microsoft Visual Basic for Applications editor. Click Insert > Module to add a new module, and paste the following code into the module:
Sub TwoWayLookupMacro()
Dim tblRange As Range
Dim rowLabel As String
Dim colLabel As String
Dim rowIdx As Variant
Dim colIdx As Variant
Dim result As Variant
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set tblRange = Application.InputBox("Select the table range for lookup", xTitleId, Type:=8)
rowLabel = Application.InputBox("Enter the row label to find", xTitleId, Type:=2)
colLabel = Application.InputBox("Enter the column header to find", xTitleId, Type:=2)
On Error GoTo 0
rowIdx = Application.Match(LCase(rowLabel), Application.Index(tblRange, 0, 1), 0)
colIdx = Application.Match(LCase(colLabel), Application.Index(tblRange, 1, 0), 0)
If IsError(rowIdx) Or IsError(colIdx) Then
MsgBox "Row or column label not found. Please check your input.", vbExclamation, xTitleId
Exit Sub
End If
result = tblRange.Cells(rowIdx, colIdx).Value
MsgBox "The value at the intersection is: " & result, vbInformation, xTitleId
End Sub2. To run the macro, click the 
button or press F5. You will be prompted to select your table range and input both the row and column labels. The macro will return the value at their intersection in a pop-up dialog box.
Practical tips:
- Make sure your table headers are located in the first row and first column of the selected range for accurate matching.
- This macro uses case-insensitive matching by converting input to lowercase, which aids in avoiding common capitalization errors.
- If your table layout is different, you may need to adjust the macro for proper indexing.
- For more complex use cases, VBA code can be extended to handle batch lookups or write results directly to Excel cells.
If you encounter issues, such as not finding headers, verify that there are no leading/trailing spaces or hidden characters in the labels and data range.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in