At the Intersection of Speed and Excellence

Nifty Tip #4: Organizing the Data Table for Proper Use of the VLOOKUP Formula

By admin • February 9, 2010 • Filed in: Nifty Tip

The VLOOKUP formula searches for the lookup criteria in the leftmost column of the data table.  It is recommended that the whole sheet be used as the data table, so that the VLOOKUP will automatically look at column A as the leftmost column.  Defining a Name for the sheet (to use it as the Table_array, the second argument in the VLOOKUP formula) will eliminate the need of updating the range reference in any VLOOKUP formula.

To define a Name for the sheet:

  1. Select a cell and click Select All (the button a the top left corner of the intersection between rows and columns).

OR

Press <ctrl>+A (In Excel 2003, press <ctrl>+A+A when selecting a cell in a region).

  1. Press <ctrl>+F3, and in the Names workbook box, type the name for the data table.
  2. Click OK.

Related posts:

  1. Nifty Tip #2: Combining the VLOOKUP and MATCH formulas
  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

 

Leave a Comment

« | Home | »