How to Do Vlookup in Excel

Sometimes you have a table with many rows and columns and you want to rearrange the information or find information about specific customers from it. In this case, the Vlookup command can be very useful. I will explain this command with two different examples.

How to Use Vlookup in Excel

There are two important points about this command

1- Whatever you are looking up has to be on the left-hand side of the table.

For instance, in this example, we have some names and for each of them, we have data which we should find them and put them in front of those names in the new table.

Here we are looking for Names and they are on the left-hand side of the table.

If our names were placed in the middle of the table we could not use this formula for the columns which they were placed on the left side of the Name column.

2- Col_Index_Number

To clarify it I will explain this subject from the following example;

In this example, our table has 5 columns and we want to rearrange its data according to the first column.

In this example the name of columns are:

Name=1, Number=2, Goods=3, Price=4, Total Price=5

First Example

At the first example, we have one table, we want to rearrange its data by this command.

As you see we have two tables and each table was placed in one sheet.

At first, we want to rearrange the data of column second column.

Vlookup formula has four part:

• lookup_value

For this part, you should select the cell in the empty table which you want to find its data in the main table.

• table_array

Go to the sheet which the main table is placed and select the table.

• At the first part, you see “table!” in the formula, the reason for that is our table is placed in another sheet.

• You should fix the range. For that, you should select the range and click on F4 key in the keyboard after that the shape of the range will be changed from " B3:F11" to “$B$3:$F$11”. you can also type the range in formula in this way.

• col_index_num

Because Number column is placed after Name column in the table, “col_index_number” value equal two.

• range_lookup

For this part, you should write False or number zero in the formula.

=Vlookup(C3,

=Vlookup(C3,table!$B$3:$F$11,

=Vlookup(C3,table!$B$3:$F$11,2,False)

Specify this formula to other cells of the second column.

For third column the formula is “=VLOOKUP(C3,table!$B$3:$F$11,3,0)”

In the Range_lookup you can write 0 or False.

For forth column, the formula is “=VLOOKUP(C3,table!$B$3:$F$11,4,False)”

For fifth column the formula is “=VLOOKUP(C3,table!$B$3:$F$11,5,0)”

specify the formula for the other cells of the table.

you can see, the data of the second table were rearranged automatically.

Second Example;

In this example, we have some number and we want to know If these numbers exist in the table or not.

for that you should follow these steps;

In the table which you want to check the data in it write this formula;

=VLOOKUP(H3,$C$11,1,0)

• In this formula, H3 is the address of the cell which you want to check its data in the second table.

• Because we only want to check the data in one column we select that column in the original table and also we should fix it. for that, you can place $ sign before each character of the range or select the range and press F4 key in the keyboard.

• we wrote number 1 in this part, because we only have one column.

• we can write False or number 0 in this part.

• Specify this formula to other cells of the column too.

• you can see this text in some cells “#N/A”.

If you like you can change it by each sentence which you like by this formula

IfERROR(VLOOKUP(H3,$C$11,1,0),“Invalide Code”)

• Here we have chosen the “Invalid Code” message, but you can choose each sentence which you like except that.

• Specify this formula to other cells of the column too.