Perform Two-Way Lookups in Excel: A Comprehensive Guide
Two-way lookups in Excel enable users to accurately and efficiently retrieve specific values from a large dataset by simultaneously identifying both a specific row and a specific column within a table. Unlike simple one-dimensional lookups, two-way lookups take into account two criteria: one located in the row headers and another in the column headers, ensuring precise and contextual data extraction. This capability significantly enhances data analysis, reporting, and decision-making processes in Excel. Below are three effective methods to implement two-way lookups.
Let’s take the following sale report for example, I want to retrieve the sales figure corresponding to the month of June and the city of Paris.
Perform Two-Way Lookups in Excel
Method 1: Perform Two-Way Lookups with INDEX and MATCH functions
Combining the INDEX and MATCH functions is a flexible, robust, and reliable solution, compatible with all Excel versions, including older ones. INDEX returns a value from a specified position within a table, while MATCH locates the position of a value in a range.
Copy or enter the following into a blank cell to output the result, then, press Enter key to get the result:
=INDEX(B2:F13, MATCH(I1, A2:A13, 0), MATCH(I2, B1:F1, 0))
- MATCH(I1, A2:A13, 0) finds the row position of "Jun" in the month column;
- MATCH(I2, B1:E1, 0) finds the column position of "Paris" in the city headers;
- INDEX returns the value at the intersection of row 6 and column 2 in range B2:E13.
Overcome VLOOKUP Limits with Kutools: Super LOOKUP
To address the limitations of the VLOOKUP function in Excel, Kutools has developed multiple advanced LOOKUP features, offering users a more powerful and flexible data lookup solution.
- 🔍 LOOKUP Across Multiple Sheets...: Perform lookups across multiple worksheets to find matching data.
- 📝 Multi-condition Lookup...: Search for data that meets multiple criteria simultaneously.
- ➕ LOOKUP and Sum...: Search for data based on a lookup value and sum the results.
- 📋 One-to-many Lookup (returns multiple results)...: Retrieve multiple matching values for a single lookup input.
- Get Kutools For Excel Now!
Method 2: Perform Two-Way Lookups with XLOOKUP (Excel 365, Excel 2021)
The XLOOKUP function simplifies the two-way lookup significantly and is recommended if you’re using Excel 365 or Excel 2021 onwards. It is concise, intuitive, and more user-friendly compared to INDEX/MATCH.
Copy or enter the following into a blank cell to output the result, then, press Enter key to get the result:
=XLOOKUP(I1, A2:A13, XLOOKUP(I2, B1:F1, B2:F13))
- The inner XLOOKUP finds the "Paris" column and returns the entire column array;
- The outer XLOOKUP finds "Jun" in the month column and returns the corresponding value from the Paris column.
Method 3: Perform Two-Way Lookups with VLOOKUP and MATCH functions
Combining VLOOKUP and MATCH provides another familiar method, especially useful for users comfortable with VLOOKUP. It’s widely used, effective, and compatible across nearly all Excel versions.
Copy or enter the following into a blank cell to output the result, then, press Enter key to get the result:
=VLOOKUP(I1, A1:F13, MATCH(I2, A1:F1, 0), FALSE)
- MATCH(I2, A1:F1, 0) finds the numeric position of "Paris" within the headers, indicating the column index to VLOOKUP.
- VLOOKUP(I1, A1:F13, column_number, FALSE) locates the row based on the month "Jun" and retrieves the intersection value from the matched column.
Conclusion:
For modern Excel users, XLOOKUP provides the most intuitive solution. INDEX/MATCH remains the most versatile for compatibility across versions. VLOOKUP/MATCH works but has more limitations. Choose the method that best fits your Excel version and specific requirements. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Related articles:
How to vlookup between two dates and return corresponding value in Excel?
How to vlookup to return multiple values in one cell in Excel?
How to vlookup next largest value in Excel?
How to vlookup to another workbook?
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