CATEGORY: Lookup Formulas VERSIONS: All Microsoft Excel Versions

Rules for organizing the data table for proper use of the Vlookup formula: 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 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 at 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.) 2. Press Ctrl+F3, and in the Names in workbook box, type the Name for the data table. 3. Click OK.

Screenshot // Vlookup Formula – Organizing the Data Table

Rate this tip 1 2 3 4 5   RATING: 2.69   VIEWS: 155295 Email Tip Print Tip Add to My Tips

READER COMMENTS (view all comments)

PMT Formula Arvind wrote on December 31, 1969 19:00 EST I wants to know in detail what is PMT formula in excel and how it calculates the figures. For exxample : we have loan amount is : 20000 Interest rate is : 8% Time period is : 12 x 3 = 36 month after applying PMT value is cominf 626.73. please let me know how 626.73 is coming. Arvind 9891051016 how do we compare to columns praveen wrote on December 31, 1969 19:00 EST Hi, I want to compare 2 columns and want to get common values in the 3rd column. How can i do this in excel? Thanks and regards, Praveen... How to assign a maximum limit for a value Rajendra wrote on December 31, 1969 19:00 EST How do I assign a vaule for a cell which should not be increased beyond specified value How to compare 2 columns Ramesh wrote on December 31, 1969 19:00 EST I have data in 2 columns i.e. A & B. I want to find out whether column A value available in Column B. A B x1 y1 x2 z1 x3 x1 x4 x2 In the above 2 columns x1 & x2 is available in both the column. I want to know how to do the same. PMT Sharareh Razavi wrote on December 31, 1969 19:00 EST i want to know in detail what is PMT formula in excel and how it calculates the figures. For exxample : we have loan amount is : 20000 Interest rate is : 8% Time period is : 12 x 3 = 36 month My Problem is like to Arvind, please guide me as soon as possible Razavi Managing Director MOBILE:0098 912 2836025 Office: 0098 21 22953000 Email: shrazavi@rdonet.com Fax: 0098 21 22960113 after applying PMT value is cominf 626.73. please let me know how 626.73 is coming. Diffrence between DATE?TIME Bhavesh Bhatt wrote on December 31, 1969 19:00 EST I have table with two field User, Date I want to calculate difference between two user for perticular user.

REGISTERED USERS - Click here to post comments GUESTS (Click here to register) Name Comment Title Comments

Marketing Plans Special Edition Using Microsoft Outlook 2002 The Guide to Understanding Financial Statements Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider Absolute Beginner's Guide to Microsoft Excel 2002 MP Managerial Accounting w/ Topic Tackler, Net Tutor, & PowerWeb

RELATED MICROSOFT EXCEL TIPS

Create User Forms in Microsoft Excel Using Loops in VBA in Microsoft Excel The Best Shortcut Keys in Microsoft Excel Writing Your First VBA Function in Excel Add Combo Box to a Sheet in Microsoft Excel Formulas to calculate Loan payments in Microsoft Excel Array variables using VBA in Microsoft Excel Create an Amortization Schedule in Microsoft Excel Find duplicates items in combine columns in Microsoft Excel Vlookup Formula – Organizing the Data Table

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