X

How to use VLOOKUP function in excel?

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.

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.
      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)
    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.
    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)
    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)
      Here, 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.
    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?

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

      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?
Related Post
Categories: Excel vlookup
Kaival:

View Comments (6)

  • There are two more issues I was wondering about. First, you suggested writing different col_index_num for debit and credit. It may be feasible for example. But in practical scenario there are about 5000 ledgers in a Trial Balance. It won’t be practically feasible to change col_index_num for each ledger. Is there any solution for the same…?
    Secondly, in practical life.. It’s not thumb rule that a ledger will always have debit balance only or credit balance only. Like, a debtor’s account having debit balance in one financial year may have credit balance in subsequent year. For better understanding i hereby provide you an example.
    Debtor A has debit balance of Rs. 30 Lakhs in F.Y. 2008-09 and credit balance of Rs. 10 Lakhs in F.Y. 2009-10. Now as per your method of fetching values the data will be represented as under:
    Particulars Debit Credit Values fetched through lookup
    A 30,00,000/- – 10,00,000/-
    Now normal analysis will show there’s difference of Rs. 20,00,000/-, However, actual difference is of Rs. 40,00,000/-. How to tackle the same issue…?

    Waiting for your reply…

    • Hello Rupal Kakhani,

      Two issues stated by you would not arise in this method. See how..

      Just take down debit column only and credit column in the next column to it.

      Issue 1:

      1. First of all, you copied Dr. column to the the end as per pic 9.
      2. Now go to the next right column. (i.e. right cell of "39" answer. or you can say 4th column from capital word.)
      3. Write vlookup formula again i.e. =vlookup(A6,$H$6:$J$17,3,0)
      4. Drag down the formula to end.

      Issue 2:
      Talking about 2nd issue, you have 4 column 2015-16 Dr, 2015-16 Cr and 2016-17 Dr & 2016-17 Cr.

      1. Reduce Dr. balance from Dr.balance and Cr. from Cr. in new two columns.
      2. Get net difference to track such Debtors, creditors etc.

      You can find demo file on the blog.

      Thanks,

  • Mr Kaival patel I seem so u have great knowledge of excel what an amazing blog covering whole function in very simple and practical terms.And surely I will stay connected and will wait for your future blog.All the best….

Leave a Comment

This website uses cookies.