|
» 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 |
   |
|
RATING:
2.63
VIEWS: 96512 |
||||
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
Sponsors:
 |
Book Store:
Recommended Books:
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
- The Intelligent Investor: The Definitive Book On Value Investing, Revised Edition
- Absolute Beginner's Guide to Microsoft Office Excel 2003
- Financial Statements: A Step-By-Step Guide to Understanding and Creating Financial Reports
- Wall Street Journal Guide to Understanding Money and Investing
- Writing Excel Macros with VBA, 2nd Edition
Related MS EXCEL TIPS:
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.
