VLOOKUP function

Introduction

Excel VLOOKUP function is best suited for situations when you are looking for a matching data point in a column (Vertical list), and when the matching data point is found, you go to the right in that row and fetch a value from a cell which is a specified number of columns to the right.

Let’s take a simple example here to understand when to use Vlookup in Excel.

Remember when the exam score list was out and pasted on the notice board and everyone used to go crazy finding their names and their score (at least that’s what used to happen when I was in school).

Here 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.

And that is exactly what the Excel VLOOKUP function does for you (feel free to use this example in your next interview).

VLOOKUP function looks for a specified value in a column (in the above example, it was your name) and when it finds the specified match, it returns a value in the same row (the marks you obtained).

Syntax

= VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Input Arguments

• lookup_value – this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name.
• table_array – this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains score for everyone for every subject
• col_index – this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2.
• [range_lookup] – here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match (see additional notes below).

Additional Notes

• As noted that range_lookup could be either FALSE, which indicates 0 = exact match
• If it is not false, then, it could be TRUE, which indicates 1 = approximate match
• For the approximate match to be correct, ensure that the data is sorted in ascending order (top to bottom)
• Note that in approximate mode (TRUE),
• If the VLOOKUP function can not find the value, it returns the largest value, which is less than the lookup_value.
• It returns a #N/A error if the lookup_value is smaller than the smallest value.
• If lookup_value is text, wildcard characters can be used (refer to the example below).