VLOOKUP VS INDEX/MATCH

Subscribe my youtube channel!

 

VLOOKUP / INDEX / MATCH are created in category reference and lookup function in Microsoft Excel.

Main function of VLOOKUP is look for a value in the leftmost column of a table and retrieve data from a specific column of that table.

Using combination of INDEX and MATCH we can return a specific value like in the VLOOKUP case.

In this first example i have a data table with 4 columns:

index1

I want to return the city of Mark (Madrid) using VLOOKUP:

index2

Three important aspect:

  • When i use the Vlookup, the lookup_value (Mark), must be the first column (column B) at the left of the table array selected
  • You must have to select all the columns between the column with the lookup_value and the column that you want to return the value (in this example select range $B$1:$D$5)
  • Cannot return a value, in a column that is at the left at the column that have the lookup_values
  • In this example, the value returned is in column of the table_array with index 3 (column B index 1;column C index 2; column D index 3)

Making now the same exercise using INDEX/MATCH.

First the MATCH function. This function returns the relative position of an item in a array. In the next example i want to find the position of Mark in the array of names:

image1

Mark is the name i want to find (A10), in the array of names (B2:B5). Position 4 of the array is returned:

match_function

match_result

Now the function INDEX. This function returns a value or reference of a cell using the intersection of a particular column or/and row.

I know that the city that corresponds to Mark is Madrid and corresponds to raw 4 of column D

Making a simple function using column D i get:

index_1

Position 4 of array D2:D5 returns value Madrid.

You learn that you can return the raw position using the function MATCH, position that you use in the INDEX function (number 4 in second parameter). So you can “mix” the two function returning the final value (City of Mark=Madrid):

index2

Advantages of INDEX/MATCH:

  • With this two function you have the flexibility that VLOOKUP cannot give you. It’s not mandatory that the lookup value is at the leftmost column (it can be in any place)
  • If you have a big data range, the processing time is higher using VLOOKUP because in this function you must select all range between column with lookup_value and the column with the final value that you want to return (if lookup_value is in column A and final value in column Z, you must select all columns from A to Z).Using INDEX/MATCH jut need to select column A and column Z.

 

Excel file:   index_match

 

Different base numbers

In 99% of the time that we use numbers, decimal system is used.
This video show the different types of number systems used, and that have a relevant role in the computer science, and how the computers “watch” the data

Welcome

Welcome

This is my first post.

My name is Luis Carlos and i’m from Portugal.
Sharing and acquire Knowledge is my goal on this Blog/Website. Acquire knowledge give us many benefits, help us grow, give recognition, motivation and new ideas.

In the next posts i will share with you some videos and tutorials about productivity tools used on a daily basis.

Your comments will be an added value for this page.

Thanks!!!