At the Intersection of Speed and Excellence

Nifty Tip #2: Combining the VLOOKUP and MATCH formulas

By admin • January 22, 2010 • Filed in: Nifty Tip

The VLOOKUP formula returns data from any column you choose in the data table.  All you have to do is change the number of the column in the third argument, right?

Well, it sounds easy, but there is a little 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 One:  Define Two Names

  1. Select Row 1, press <ctrl>+F3 (That’s the F3 function key at the top of your keyboard.  It opens the Name Manager).
  2. Type the name Row1 in the Names in workbook field, and click OK.
  3. Select the data table by pressing <ctrl>+*.  Then open the Name Manager again by pressing <ctrl>+F3, and enter Data in the Names in the workbook field, then click OK.

Step Two: Enter the Match Formula

  1. Open an adjacent worksheet, and select cell A1
  2. In cell A1, type 4/01/2001
  3. In cell B1, enter the formula =MATCH(A1, Row1, 0)  Hint: 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 Three: Enter the Vlookup formula

  1. Enter the account number 201 into the 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: 7981.

Step Four: 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, a nested formula is:

=VLOOKUP(A2, Data, MATCH(A1,Row1,0))

Related posts:

  1. Nifty Tip #4: Organizing the Data Table for Proper Use of the VLOOKUP Formula
  2. NIFTY TIP #5: Adding or Subtracting Amounts from Different Ranges
  3. Nifty Tip #3: CONCATENATE
  4. Figart Consulting’s Excel Nifty Tip #1.5
  5. Figart Consulting’s Excel Nifty Tip #1

Comments

By Tracy on May 14th, 2010 at 4:47 pm

Absolutely brilliant! Exactly what I have been struggling to do for the past 3 hours.

 

Leave a Comment

« | Home | »