Monday, May 11, 2020

Home Excel tips How Excel VLOOKUP Works: How to use it – Explanation with Example

How Excel VLOOKUP Works: How to use it – Explanation with Example

May 11, 2020 Excel tips,

How Excel VLOOKUP Works: How to use it – Explanation with Example

ile explaining LOOKUPs in my previous post, I explained how in daily life we apply lookup functions in different situations and to understand better I explained the example of mathematical lookup table to find the rate. If you haven’t read that article yet then I strongly recommend to read that first and then come to this tutorial to understand the application VLOOKUP formula in Excel.

To make it easier for you to jump from the basic example to VLOOKUP example I will be using the same data converted into excel format. Meaning I will have the same Annuity table inside Excel and you will be surprised that function you were dreading for so long is actually way easier to apply and work with.

Download Excel Tutorial Workbook

To best understand the concepts and examples in this article download this excel workbook and follow the steps discussed.

VLOOKUP – Demystified!

Syntax of VLOOKUP formula is:

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

lookup_value: this is the value you want to lookup (find)

table_array: consider it a list of items that are arranged in numerous columns and rows. This basically makes up the whole area which you want to include in your search for specific value.

col_index_num: once the lookup value is found, this number tells excel from which column the value should be fetched corresponding to value looked up.

[range_lookup]: This is an optional argument. It can either be TRUE or FALSE. This is mentioned to dictate Excel if one wants an exact match or an approximate match. If it is left out empty then by default it is TRUE i.e. approx match for the lookup value.

In layman terms all of this can be said as:

Look for specific value (mentioned as lookup_value) from top to bottom (V in VLOOKUP is for vertical).
In the first column of the range (mentioned as table array which has several rows and several column). Whether the value to be found is by approximate [TRUE or 1] match or an exact match [FALSE or 0] depends on the option mentioned as [range_lookup]. Once found remember the row on which the value was found.
Jump x number of columns to right to reach the specific column (x is mentioned as col_index_num) from the first column in the specified range (table_array) and fetch the value which is on the same row on which lookup_value was found.

Quick Summary

So basically VLOOKUP tells Excel to initiate search in vertical manner going from top to bottom looking for the value in the first column of the specified range. Once value is found jump or skip x number of columns to reach the intended column and fetch the corresponding value on the same row from the column reached. Whether the match to be made is exact or approx depends on the option i.e. if exact option is selected and no exact value found then formula will return an error.

Basics Example - Revisited

Let’s step back a little and before I move on to explain the VLOOKUP application on annuity table, lets revise the steps we learned before:

Step 1: Identify the column containing number of years. That is the first column of the table.

Step 2: Find the number (of year) that we want. Once found halt at that row.

Step 3: Now jump to the column at right to reach the one that is of correct interest rate. In our example we have to jump to 9th column i.e. 9th column (column of interest rate with 4%) from the first column (column of years).

Step 4: Fetch the value that is in the same row where the number of year is. In simple words where the column of 4% and the row of 7 years intersect bears the value. In our example the value is 6.00205

Applying your first ever VLOOKUP!

Now we have the same exact annuity table in Excel and lets apply VLOOKUP formula on this example to get the results super quickly saving us the labour of searching the annuity factor manually.

Open the workbook you downloaded for this tutorial. If you haven’t downloaded it yet then download the file by clicking here.

So what do we want?

We want annuity factor if it is for 7 years with the rate of 4%.

Look at the data and understand few things:

Our intended data starts from cell A4 and runs until cell S27. So in short the range that enclose all the data in Excel language is written as: A4:S27
Column A contains number of years, and we will use this column to search for year 7. That is why it will also be included in the search range.
Row 3 is a header row containing rates. We want annuity factor based on 4%. Column containing annuity factor based on this rate is 8th column in the range. As we will be manually telling Excel the column number in VLOOKUP formula or function therefore we don’t have to include header row in our search range.

Step-by-Step

Now we are all set to apply the formula. So scroll down to cell F31. Its just under the annuity table on same worksheet. Follow these steps to insert the function:

Step 1: Once you or on cell F31. Press F2 to get in edit mode or simply double click with your mouse on that cell.

Step 2: In Excel if you have to insert function it always start with “=” sign. So hit “=” button on the keyboard. Mostly it is sits at left to backspace key.

Step 3: Start typing VLOOKUP. The moment you start typing, Excel will start suggesting different functions. You can either type the whole name yourself or select from the tool tip or suggestions using directional arrows on the keyboard. But one thing to remember that if you select from suggestions and want to insert the selected function you DON’T press Enter instead you press TAB to insert the function correctly.

Step 4: Once function name is inserted it will be like: =VLOOKUP(

Now the tool tip or suggestion is telling you about different arguments you have to put in place. Type 7. This is your lookup_value. Hit comma key on the keyboard. You will notice that in the tooltip that moment you press comma table_array will get bold that means now you can insert this argument. This is a nice way to walk you through the formula without mistakes.

Back to function, we have to tell function about the range in which search should be done. We already know the range which is A4:S27. You can type it or select the cells using mouse. To do the selection with mouse go to cell A4, click left mouse button and hold and while holding drag your mouse to cell S27 which is the last cell of the range. Once selected, release left mouse button. Hit comma button.

Step 5: Now you are in the third argument which is col_index_num. Put 8. As the 4% rate is the 8th column in the range. Close parenthesis by holding down Shift key and pressing 0 on the keyboard which puts “)”. Hit Enter key.

Bingo! 6.00205 is the value.

Let’s recap the formula once again which in actual is:

=VLOOKUP(7,A4:S27,8)

Putting it in words it is:

Lookup for the figure 7 in the first column of the range A4:S27 which is A4:A27. Once found, remember the row. The row is 10. Now move to 8th column in the range with is Column H and get the value on Row 10 in the column H which is 6.00205.

Matching the arguments we provided in this formula with the syntax we learned in the beginning it is:

lookup_value is 7

table_array is A4:S27

col_index_num is 8

[range_lookup] is TRUE as if nothing is mentioned Excel assumes it as TRUE

VLOOKUP – DIYs

Now that you have officially graduated in VLOOKUP formula. You are free to experiment with it. Some of the ideas are:

Lookup annuity factor for year 10 – For this formula will be: =VLOOKUP(10,A4:S27,8) and answer is: 8.1109
Lookup for annuity factor for year 15 with interest rate 10% – For this formula will be: =VLOOKUP(15,A4:S27,16) and the answer is: 7.60608