Index Formula vs. Vlookup Formula

My Account

Check out

Shopping Cart

JOIN OUR NEWSLETTER
and receive for joining:
	Free eBook Learn More!
	Free Excel ADD-IN
	Free Weekly Excel Tip








F1 eBook (Spanish)

» Index Formula vs. Vlookup Formula

CATEGORY: Lookup Formulas

VERSIONS: All Microsoft Excel Versions

What are the advantages of using the Index Formula instead of Vlookup Formula?

How the Index formula calculates and returns results:

The Index formula returns a value from the intersection between the row number and the column number in an Array. For example, in the screenshot below, the amount of Petty Cash for December 2003 is 585, which is the intersection cell of row 7 and column 29.
The Index formula syntax is: =Index(Data, 7, 29), where Data is the defined Name for the sheet.

How the Vlookup formula calculates and returns results:

The Vlookup formula returns a value from the intersection between the found lookup value in the leftmost column and a column index number in the Table Array. For example, in the screenshot above, the Account Number (column A) for Petty Cash is 1091, the column number for December 2003 is 29, and the amount for Account Number 1091 and December 2003 is 585.
The Vlookup formula syntax is: =Vlookup(1091, Data, 29).

So, which one is better to use?

The main difference between the two formulas is that when using Index, there is no need to organize the data table into a special format – you simply find the intersection between a row and a column. When using Vlookup, though, you always need to move the lookup column to the left side of the data table.

Use the Match formula to find the row, column and index column number when using both Vlookup & Index formulas
The Match formula returns the cell number where the value is found in a horizontal or vertical range. For example, look at row 3 in the screenshot below. December 2003 is in cell number 29 in row 1 (the Name defined for the list is Row1), see the screenshot on the previous page.
In row 7 in the screenshot below, two Match formulas are nested in an Index formula. The first calculates the row number where the text Petty Cash is found (row 7) and the next one calculates the column number for December 2003 (number 29).

Screenshot // Index Formula vs. Vlookup Formula

Rate this tip



1	2	3	4	5

RATING: 2.62
VIEWS: 78885

Email Tip

Print Tip

Add to My Tips

READER COMMENTS (view all comments)

Formula for Carrying VLOOKUP AND HLOOKUP FUNCTION
Shaikh Mohammed Ali wrote on December 31, 1969 19:00 EST

I would love if the formula for calculating Vlookup and Hlookup is made available clear

The Fall of Advertising and the Rise of PR

Special Edition Using Microsoft Outlook 2002

Wall Street Journal Guide to Understanding Money and Investing

Investing in Real Estate, Fourth Edition

Essentials of Accounting and Post Test Booklet 8, Eighth Edition

Accounting Principles, with CD, 6th Edition

RELATED MICROSOFT EXCEL TIPS

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

VIEW ALL BOOKS

	Advertise With Us
Tips	Add-In in VBA \| Applications - Word, Outlook in VBA \| Array Formulas \| Cells, Ranges, Rows, and Columns in VBA \| Counting \| Custom Functions \| Custom Functions in VBA \| Database Formulas \| Database in VBA \| Date & Time Formulas \| Date & Time in VBA \| Events in VBA \| Excel 2003 \| Excel Chart \| Excel Consolidating \| Excel Counting \| Excel Custom Functions using VBA \| Excel Customizing \| Excel Data \| Excel Dates \| Excel Editing \| Excel Files \| Excel Filter \| Excel Format \| Excel Formula \| Excel General \| Excel Grouping and Outlining \| Excel Importing Text Files \| Excel Information \| Excel Keyboard Shortcuts \| Excel Loan Formulas \| Excel Macros - VBA \| Excel Pivot Tables \| Excel Printing \| Excel Range Name \| Excel Security - Protection \| Excel Sorting \| Excel Style \| Excel Subtotals \| Excel Summing \| Excel Text \| Excel Time \| Excel Tools \| Excel Worksheet, Workbook \| Files, Workbook, and Worksheets in VBA \| Financial Formulas \| Formating in VBA \| General Topics in VBA \| Import and Export in VBA \| Information Formulas \| Keyboard & Other Shortcuts in VBA \| Keyboard Formula Shortcuts \| Links between Worksheet and Workbooks \| Links in VBA \| Logical Formulas \| Lookup Formulas \| Mail - Send and Receive in VBA \| Menus, Toolbars, Status bar in VBA \| Modules, Class Modules in VBA \| Other Q&A Formulas \| Printing in VBA \| Protecting in VBA \| Summing \| Text Formulas \| User Forms, Input boxes in VBA \| Using Loops \| Working with Formulas \|
Tips by Version	Microsoft Excel 97 \| Microsoft Excel 2000 \| Microsoft Excel 2002 \| All Microsoft Excel Versions \| New in Excel 2002 \| New in Excel 2003 - Office 11
Website	Home \| Tip of Hour \| Recommended Tips \| Most Viewed Tips \| Tips by Version \| Submit a Tip \| My Tips Microsoft Excel Tutorials \| Excel Links \| Write for Us \| About Us \| Search Results \| Tip Archives \| Excel Forum \| Excel Forum Archives
Excel Book	Excel 97 Book \| Excel 2000 Book \| Excel 2002 Book \| Excel XP Book \| Book Store

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.
Copyright © 2003 ExcelTip.com Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation Site Developed By: Varien