 Tweet

# Excel Tips Tutorial: How To Use Vlookup in Microsoft Excel

Vlookup is very useful option in Microsoft Excel, It looks up the value in the left most column of the table and returns a value in the same row from a column you specify.

The formula of Vlookup is =vlookup(lookup_value,table_array,col_index_num,[range_lookup])

Lookup_value is the value which you want to find from the table

Table_array is the table in which you want to find the value

Col_index_num is the column in which the desired value is available in the table

Range_lookup can be Approximate match or Exact match

I am giving you an example of how to use Vlookup in Excel In the example above we have the data of our clients with addresses written with them, you have some names written below which are obviously not written with the same sorting as written above. Now what we want is to find Address and Amount receivable of our clients, the 1st option is to find addresses and amounts receivable is to find option by using Ctrl+F which will take time. This example do not have enough data so its easy to find here, but if you have thousands of data then it will take a lot of time and chances of error will increase. The 2nd option is to use Vlookup, which is very easy, saves time and obviously the chances of error is almost NIL.

So what well do is to write formula in cell B18 and find values from the above table

Well write =vlookup(A18,\$A\$7:\$C\$14,2,0)

In the formula above

A18 means the value which we want to find i.e. Alex

\$A\$7:\$C\$14 means that we have selected the table from A7 to C14, I used \$ sign with them just to constant the table because Ill copy the formula to the cells below

2 relates to the column number as from the table above we need the value of 2nd column I.e. Address.

0 means FALSE or you can say Exact match, as the names written below are exactly the same as written in the table After pressing enter you will get the Address of Alex i.e. Philippines, now what you have to do is to just copy the formula and paste it to other names, you will get the addresses of all clients. Please note that if you find any value which is not available in the table you selected, the excel will show an error of #N/A, which means that the value you want to find is not available in the table you selected. As Raymond is not in the table above it show an error here. Now in a same way well enter formula in Amount column, it will be the same as in Address column except for the column number which we have to write 3, because Amount column number is 3 (i.e. C). After pressing enter you will get the amount receivable from the client, just copy the formula again and paste it for all clients. You will get the values all correct. Vlookup can be used from sheets to sheets as well and is also available in the Formula menu in the umbrella of Lookup & Reference.    