What is VLOOKUP ?
The VLOOKUP function is the criterion.
If you know how to use the VLOOKUP function, then you know something in Excel.
If you do not, it is best not to list Excel as one of your strongest areas in your resume.
I was part of the panel interview, where as soon as the candidate mentioned Excel as his area of expertise, the first thing he asked was – did you get it – the VLOOKUP function.
Now that we know how important this Excel function is, it makes sense to be able to say with absolute pride – “I know a thing or two about Excel”.
This is going to be a huge VLOOKUP guide (by my standards).
I will deal with everything there is to know about it, and then I will show you useful and practical examples of VLOOKUP.
Look for a given value in a verticle list, and once it has spotted that value it would use that row and return the value from the specified column number
syntax for VLOOKUP
VLOOKUP(lookup_value, table_array, col_index_number, range_lookup)
- lookup_value – This is the search value you are trying to find in the leftmost column of the table. It can be a value, a cell reference or a text string. In the example of the score page, this will be your name.
- table_array – This is the table array where you are looking for the entry. This can be a reference to a cell range or a named range. In the grading page example, this would be the entire table with one mark for everyone for each subject
- col_index – This column is the index number from which you would like to get the corresponding value. In the example of the grading page, if you want a math grade (which is the first column in the table with the grade), look in column 1. If you want a physics grade, look in the column. 2.
- [range_lookup] –Here you specify whether you want exact match or approximate match. If omitted, the default is TRUE – Estimated Match (see more notes below).
When to use the VLOOKUP Function in Excel?
The VLOOKUP function is best suited for situations when you are looking for a matching data point in a column, and when the matching data point is located, you move to the right of this row and fetch a value from the cell that Is a defined number. To the right of the column.
Let’s take a simple example here to understand when to use VLookup in Excel.
Remember when the list of exam scores was pasted on the bulletin board and everyone was going crazy to find their name and grade (at least that’s what I used to do when I was in school).
This is how it worked:
- You go up to the notice board and start looking for your name or enrolment number (running your finger from top to bottom in the list).
- As soon as you spot your name, you move your eyes to the right of the name/enrolment number to see your scores.
Finding Brad’s Math Score
In the VLOOKUP example below, I have a list of student names in the leftmost column and marks on various topics in columns B through E.
Now let’s start working and use the VLOOKUP function that does this best. From the data above I need to know how much Brad scored in Mathematics.
From the data above I need to know how much Brad scored in Mathematics.
Here is the VLOOKUP formula that will return Brad’s math score:
The above formula has four arguments:
- “Brad: – this is the lookup value.
- $A$3:$E$10 – this is the range of cells in which we are looking. Remember that Excel looks for the lookup value in the left-most column. In this example, it would look for the name Brad in A3:A10 (which is the left-most column of the specified array).
- 2 – Once the function spots Brad’s name, it will go to the second column of the array, and return the value in the same row as that of Brad. The value 2 here indicated that we are looking for the score from the second column of the specified array.
- 0 – this tells the VLOOKUP function to only look for exact matches.
This is how the VLOOKUP formula works in the example above.
First, he looks for the entry that Brad in the leftmost column. It goes from top to bottom and gets the value in cell A6.
As soon as it finds the value, it goes to the right in the second column and fetches the value in it.
You can use the same formula structure to score someone’s marks on each subject.
For example, to find Maria’s signals in chemistry, use the following VLOOKUP formula:
In the example above, the search value (student name) is entered in double quotes. You can also use a cell reference that contains a test value.
The advantage of using cell reference is that it makes the formula dynamic.
For example, if you have a cell with the student’s name and you get a math grade, the result will be updated automatically when you change the student’s name (as shown below):
If you enter a lookup value that is not found in the left-most column, it returns a #N/A error.