Excel Two Way Lookup Formula Overview
By combining Excel's VLOOKUP function with the MATCH function, we can create what is known as a two-way or two-dimensional lookup formula that allows you to easily cross reference two fields of information in a database or table of data.
A two way lookup formula is useful when you want to find or compare results for a variety of different situations.
In the example shown in the image above, the lookup formula makes it easy to retrieve the sales figures for different cookies in different months simply by changing the cookie name and the month in the correct cells.
Excel Two Way Lookup Step by Step Tutorial
This tutorial is broken down into two parts. Following the steps listed in each part creates the two way lookup formula seen in the image above.
The tutorial involves nesting the MATCH function inside of VLOOKUP.
Nesting a function involves entering a second function as one of the arguments for the first function.
In this tutorial, the MATCH function will be entered as the column index number argument for VLOOKUP.
Tutorial Contents
Part 1
- Entering the Tutorial Data
- Creating a Named Range for the Data Table
- Starting the VLOOKUP Function
- Entering the Lookup Value Argument
- Entering the Table Array Argument
- Starting the Nested MATCH Function
- Adding Data Ranges for the MATCH Function
- Adding the Match type and Completing the MATCH Function
- Completing the VLOOKUP Function
- Adding Search Criteria to Test the Completed Formula
- Copying the Two Dimensional Lookup Formula with the Fill Handle
