» Combine the VLookup and Match formulas in Microsoft Excel

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

1. 
   Select Row 1, press Ctrl+F3, type the name Row1 in the Names in workbook field, and click OK.
2. 
   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

1. 
   Open an adjacent worksheet, and select cell A1.
2. 
   In cell A1 type 4/1/2001.
3. 
   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.)
4. 
   Results of calculation: 7.

Step 3: Enter the Vlookup formula

1. 
   Enter the account number 201 into cell A2.
2. 
   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.
3. 
   Calculation results: 7,981.

Step 4: Combine the formulas

1. 
   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.
2. Press Ctrl+V and press Enter.
3. 
   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
   
   

   Book Store:

   • Excel VBA books
   • Accounting books
   • Business Plans
   • MS Office books
   • Taxes books

   VIEW ALL BOOKS

   Recommended Books:

   • Financial Shenanigans : How to Detect Accounting Gimmicks & Fraud in Financial Reports
   • Monte Carlo Methods in Finance
   • Windows XP Annoyances
   • Keys to Reading an Annual Report (Barron's Business Keys)
   • Special Edition Using Microsoft Word 2002
   • Investing in Real Estate, Fourth Edition
   Email Tip

   Print Tip

   Add to My Tips

   Rate This Tip

   1 2 3 4 5

   Rating: 2.46     Views: 305620

   No comments have been submitted.

   Click here to post comment

   For Registered Users

   Click here to Regiser

   For Guest

   Name

   Comment Title

   Comments