Hello friends

I would like to share what I know about Vlookup, Index and Match, which are the functions I need most when preparing digital marketing campaigns and web analysis reports.

It provides convenience in situations such as comparing a large number of data, finding the information in a cell in a different index. Thanks to these functions, you can complete operations such as searching and matching that would take hours in a very short time.

First, let’s understand the working principle of the Vlookup function.

Vlookup

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value : value to look for

table_array : array to search for the value

col_index_num : how many columns in the array the function will return as a value

range_lookup : exact match / close match selection (FALSE / TRUE)If we want to look at an example array and value;

The function looks for Customer 11 in the table on the left, and returns the value in the second column of the row where it finds Customer 11 in the array.

This way, we can get the phone number of customer 11.

You can also use this function to compare more data. Double click on the bottom right of the box with the function and the formula will apply to the other rows as well.

As in the example above, if customers are listed in reverse order, all the correct phones will be placed in the cells as the return value of the function.

Index and Match Duo

Because Vlookup performs more comparisons, it consumes high performance and requires a long time when applied to very long arrays. If you do hundreds of thousands of searches on an array of hundreds of thousands of rows, it can take hours or even days for Excel to finish.

In order to shorten this time, I would like to mention the Index and Match functions as an alternative.

Match Function

MATCH(lookup_value, lookup_array, [match_type])

lookup_value : value to look for

lookup_array : the column to look for

match_type : exact match / close match selection (FALSE / TRUE)

The main feature that distinguishes the Match function from the Vlookup function is that the search can only be done in one column, as you can see in the top table. Also, the function returns the index of the row it finds, not the value in the cell.

Index Function

INDEX(array, row_num, [column_num])

array : array

row_num : row index

column_num : column index

The Index function looks at an array and uses the row and column indices to return the value in the cell it finds in that array.

Used together, after finding the row index with Match, we can return the value at that row index and the 2nd column with Index. As a result, although there are no differences with Vlookup, there are big differences in the way they work.

Especially for very large arrays, using Index / Match is more advantageous than Vlookup.

Thank you very much.