Combine the VLookup and Match formulas in Microsoft Excel

» Combine the VLookup and Match formulas in Microsoft Excel

VERSIONS: All Microsoft Excel Versions

The VLookup formula returns data from any column you choose in the data table, simply change the number of the column in the third argument.

Although it sounds simple, there is a catch.

How can you determine the number of a column in a data table that contains numerous columns?
How can you easily change the number of the column in the third argument of the VLookup formula?
How can you easily change the column number in multiple formulas in a worksheet from which complex reports are prepared or in a sheet that contains multiple VLookup formulas?

Solution

Insert the Match formula in the third argument of the VLookup formula.

Step 1: Define 2 names, see screen shot

Select Row 1, press Ctrl+F3, type the name Row1 in the Names in workbook field, and click OK.
Select the data table by pressing Ctrl+*. Then press Ctrl+F3, enter Data in the Names in workbook field, and click OK.

Step 2: Enter the Match formula

Open an adjacent worksheet, and select cell A1.
In cell A1 type 4/1/2001.
In cell B1, enter the formula =MATCH (A1, Row1, 0).
(Be careful to enter the value 0 in the third argument to specify the search for an exact value.)
Results of calculation: 7.

Step 3: Enter the Vlookup formula

Enter the account number 201 into cell A2.
Enter the formula =VLOOKUP(A2,Data,B1) in cell B2. In the third argument of the VLookup formula, select a cell which contains the Match formula.
Calculation results: 7,981.

Step 4: Combine the formulas

In the formula bar (Match formula) of cell B1, select the formula without the = sign, press Ctrl+C, and click the Cancel sign (from the left of the formula in the formula bar). Select cell B2, and in the formula bar, select the address B1.
Press Ctrl+V and press Enter.
The final result is a nested formula is
=VLOOKUP (A2, Data, MATCH(A1, Row 1, 0))

Screenshot // Combine the VLookup and Match formulas in Microsoft Excel

Rate this tip



1	2	3	4	5

RATING: 2.75
VIEWS: 151432

Email Tip

Print Tip

Add to My Tips

nesting as shown did not work

ranjit kumar wrote on December 31, 1969 18:00 EST

I could easily do all the steps as shown above. I appreciate the way the entire description was given but i fail to do the last step wherein the two formulas are nested. The nesting is not happening. Please provide screen shots for step NO: 4 more elaborately

Finding Problem in Multiple Results

M.Jubbar wrote on December 31, 1969 18:00 EST

=VLOOKUP(B5,Sheet1!B15:F18,2,0)
Searching Data with Vlookup function.
and chosing the resulting values one after another.
Like.
For a unique result ===> =VLOOKUP(B5,Sheet1!B15:F18,2,0)
For More then one resulting values...
=VLOOKUP(B5,Sheet1!B15:F18,2,1)
with every change in last fiqure finding the next resulting value...
Help me on this query i Shall be very thank ful....
jubbar007@yahoo.com

Multiple Condition VLOOKUP

Lana wrote on December 31, 1969 18:00 EST

Hello

I am trying to return a value that fulfills a multiple VLOOKUP, i.e. there will be multiple lines for the first condition value of 3996592, and I wish to return a value from the specific line which contains another value of 015. Is there any way to do this?

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.