So, you got here looking up to using vlookup in excel, right? Here you go. Vlookup is a very commonly used yet crucial tool of Microsoft excel. It’s been made to extract that very data you desire from that very deep pool, which is not easy to fetch one after another.

Function of VLOOKUP

=Vlookup (lookup_value, table_array, index_num, [range_lookup])

Let’s take a practical problem to get more clear and deep idea about vlookup function.

Problem 1: How to use of VLOOKUP when comparison of data is required:

Let’s say, we have two trial balance of the same company for two different years. Data if arranged like figures of F.Y. 2015-16 and F.Y. 2016-17 followed by would be easy to understand and analyses.

How to use vlookup in excel table

Here, we have two kinds of issue.

  1. The Trial balance 1 and Trial balance 2 is not having same no. of items.
  2. Trial balance 1 and Trial balance 2 is having items which are not in the same line.

If above two issues do not arise, then I wouldn’t be having writing this blog!! Lol 🙂

Step by step guide to VLOOKUP

    1. It’s easy. Just write formula of VLOOKUP on right side of the figure of F.Y. 2015-16 as shown in Pic2.Pic 2Pic 2.5
      Let’s understand the 4 arguments of VLOOKUP function one by one.Lookup value = It is the value in reference to whom data from another database is to be fetched. In given problem lookup value is of the trial balance. (In our case, it is “Capital” –> Cell = A6. (Pic3)pic 3.png
    2. Now we choose the capital word and want to search Capital word in the second table. Select the second table after comma (,).
      Table Array = It is tabular form database in which the correspondence and corresponding data to be fetched is located. Here in our example it is “TB of F.Y. 2016-17” –> Table Range = H6:J17. (Pic4)VLOOKUP only search lookup value in the first leftmost column of table array. So, select the table in such a way that your lookup value is the first column of table array.pic 4.png
    3. Now we have selected table array for our lookup value, we need to specify that if excel founds the “Capital” in the 2nd table, which no. of column we want in our answer.
      Col Index Num = Number of column you want to get as an answer if the lookup value exists in table array. In our case, Write –> 2 for Dr. Column and 3 for Cr. It would be 2 as we would like to get Dr. side first and Cr. Side later. (Pic5)
      pic 5.png
    4. Now last step, we have to select “True – Approximate Match” or “False – Exact Match”. Here you can select any of the option orWrite 0 for Exact Match & 1 for Approximate Match. (Pic6)
      Pic 6Here, we will go for an exact match. i.e. write 0. (Pic7)[Range Lookup] = If we write 0 here, VLOOKUP will find an exact match in the table 2, if vlookup could not found the value of lookup in table 2, it will give result – #N/A and if we write 1 here, VLOOKUP will find an approximate match and will return the value as taking table 2’s value as range for data.
      Pic 7.png
    5. Just hit the enter. We will get 39 as an answer.
    6. Now you just need to understand last step only i.e. giving an absolute range of table. Give an absolute reference of table in VLOOKUP formula by pressing F4 to around both the cells i.e. H6 and J17 will be $H$6:$J$17. (Pic8)Click here to know how to use Absolute, Relative and Mixed Cell References in Excel? 

      Pic 8Press Enter and Drag down the formula (Ctrl + D) to the end or copy the formula to the end of table 1. (Pic 9)

      Pic 9.png

      Conclusion:

    7. You can see the formula is working!! But wait, why #N/A in Investment in Mutual Funds? It is because we wrote 0 in range lookup and we are finding an exact match and the exact match of investment word is not present in 2nd table.
    8. Just write the same VLOOKUP formula in next column and use 3 as a column num in formula to get credit of the same ledgers. Now you will be wondering, that why someone would use Approximate Match (1) in range lookup? How can Approximate Match be useful?