Excelgoodies logo +1 236 900 6300
+1 236 900 6300  

 Business Professionals

 Techno-Business Professionals

 Technology Professionals

 Business Professionals

 Techno-Business Professionals

 Technology Professionals

Home

|

About Us

|

Contact Us

Programs & Courses

New

Home | About Us | Contact Us

+1 236 900 6300

support@excelgoodies.com

ExcelGoodies Worldwide United States | UK | Singapore

LEARN THIS HANDS ON

Microsoft Excel Reporting

ET . Live Online FILLING FAST
View all upcoming batches
Why Vlookup when there is Index?

Why Vlookup when there is Index?

 

Vlookup:
 

The syntax for Vlookup is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Index:

The Syntax for Index is:

INDEX( array, row_number, [column_number] )

Match:

The Syntax for Match is:

MATCH( value, array, [match_type] )

Problem:

For Example:

Using Vlookup In the above table we can find EmployeeName ; Dept with the help of EmployeeNumber and Dept with the help of EmployeeName, however, we are not able to find EmployeeNumber with EmployeeName and Dept because the Vlookup will work only from the right to its value and not to the left.

To fix this issue we use Index ; match

 

Solution:

Use Index first and insert Match function into it.

=Index(Data Range, #,Column number)

“#” indicates the row number to which it is referring to. So use Match function instead of “#”

=Index($A$2$:$A$4,Match(A6,$B$2:$B$6,False),1)

The number which is in the last in the index can help you with EmployeeNumber for 1, EmployeeName for 2 and Dept for 3.

Explore 60+ Functions here and become a Microsoft Excel Power User.

The Syntax for Match is:

MATCH( value, array, [match_type] )

 

Happy Excelling
Team Excelgoodies

Excel Formulas

New

Next Batches Now Live

Power BIPower BI
SQLSQL
Power AppsPower Apps
Power AutomatePower Automate
Microsoft FabricMicrosoft Fabrics
AzureAzure Data Engineering
Explore Dates & Reserve Your Spot → Reserve Your Spot →

Related Articles