Lesson#5: Function VLOOKUP and dollar sign

In this post, we will learn a very useful function VLOOKUP (Vertical Lookup, or value lookup in columns) by which we can easily find data within a table. I have made Data Validation (List) within a cell which shows the roll number.

Now I will use VLOOKUP to show the relevant student name automatically when I will enter the roll number. First, we have to understand the structure of VLOOKUP. the structure will be like this
Example: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: This is the value for which you are taking the reference from another table. Here is the roll number of students on the mark sheet.
table_array: This is the table from where we have to search for the relevant value. Here will be the marks table which I previously made on another sheet.
col_index_num: This is the column number in the table from where I have to look up the value.
[range_lookup]: It will show two options TRUE – Approximate match, FALSE – Exact match. We need an exact match here so we will select FALSE.

Look in the above picture I have selected The 12X2 table to lookup col 2.

I will select FALSE – Exact match.

Look whenever I will enter a roll number it will show the name automatically. Now I will VLOOKUP in every subject cell to look up the marks of the student which he has got in the particular subject.

Now you can see I have entered “$” in the cell reference. What is the significance of “$”? The $ sign fixes the reference which we take from another cell. When we drag the formula down or left the reference cell has also been dragged. But if we want to fix the reference then we use $ in excel. If I want to lock the column but shift the row then we will put $ before column reference. Again if I want to lock the row but shift the column then we will put $ before the row reference. If we need to take reference from only one particular cell then we have to type $ before both column and row.

Our mark sheet for individual student is almost ready. We will discuss about VLOOKUP in future posts and show you new cases where we can use it.

Related video tutorials: