EXCEL FUNCTIONS-VLOOKUP

EXCEL FUNCTIONS-VLOOKUP

INTRODUCTION

Welcome to the step by step guide to VLOOKUP in EXCEL. The article covers the introduction , basics of Vlookup as well as all the different usages of vlookup using the examples.

VLOOKUP is one of the most famous and useful functions of Microsoft Excel and is treated as an advanced level function. But advanced doesn’t mean tough. In this article we’ll try to find out what we can do with this function and how to use it. It’ll be a step wise step introduction to the Vlookup.

We prepare and manage large reports in Excel. Many times we need to join or consolidate two or more reports into a final single report but would that be possible if there are hundreds of lines in all the reports to be joined.

The Answer is NO!!!

But thanks to excel, which has given us many ways to help joining of such reports. Two of these options are VLOOKUP and HLOOKUP.

THIS ARTICLE DEALS WITH VLOOKUP, which is used a bit more than HLOOKUP.

The article will start with the purpose of VLOOKUP, after which examples using different options will be taken.

PURPOSE OF VLOOKUP IN EXCEL

“VLOOKUP enables us to LOOKUP for a particular value or text from another TABLE and return ANY content from the cells corresponding to that looked up value. “

EXAMPLE: Kindly read it for the concept building if you are a first time user.

We have a list of ROLL NUMBER of the students in a class with their names.

There is another table with ROLL NUMBERS and the complete details of the students such as NAME, ADDRESS, STANDARD etc.

Now if we want to find out any details of the students such as ADDRESS. We’ll do that by using VLOOKUP.

VLOOKUP needs a unique identifying field i.e. some value/text which is present in both the places, the looker and the table from where the item will be looked up.

Here ROLL NO. is that identifier.

After looking up the ROLL NO. we can retrieve any value present against that ROLL NO. in that table such as NAME, ADDRESS , STANDARD etc.

This is the use of VLOOKUP.

PREREQUISITES TO LEARN VLOOKUP

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  •  Of course, Excel software.

Helpful links for the prerequisites mentioned aboveWhat Excel does? How to use formula in Excel?

SYNTAX: VLOOKUP

The syntax ( the way how formula is phrased for excel) of VLOOKUP is

=VLOOKUP(cell address of value to be matched,  range of cells to search,  column number to return the value,  match should be approximate or exact)

So, a sample format is here. Suppose the value to be found is in cell H13 and the table from which the value is to be extracted has the range I12:K25 (3 columns) and the match is to be exact.The format in the output cell will be

=vlookup(H13,I12:K25,3,false)

This will find out the value of H13 in the table I12:K25 and return the value of third column i.e. K if it could find H13 in the table’s first column.

If you didn’t understand this, don’t worry, we’ll solve a few examples for better understanding.

EXAMPLE 1:VLOOKUP IN EXCEL(EXTRACTING A SINGLE VALUE FROM DATA)

DATA SAMPLE

Two tables showing the marks of different students is shown.

The scenario shows a group of two tables in which marks of six students namely, Ashutosh, Swati, Ankush, John, Daniel and Ram are given. Three subjects’ marks are in Table 1 and other three subjects’ marks are in Table 2.

The point to be noted is that Roll No. in Table 2 are in a random fashion which makes the task to be difficult a bit.

EXCEL: VLOOKUP: EXAMPLE:DATA SAMPLE

OBJECTIVE

To create a table which contains the marks of all the subjects in a final single table. Something like the table shown below.

EXCEL:VLOOKUP:EXAMPLE:REQUIRED OUTPUT

STEPS TO APPLY VLOOKUP

  • Copy the first table with the marks of three subjects.
  • Now we will start using VLOOKUP for filling up the marks of Column SST, ART AND ENGLISH.
  • As per the syntax,
    • =VLOOKUP(cell address of value to be matched, range of cells to search, column number to return the value, match should be approximate or exact
    • PUT in cell O21 , the following formula “=vlookup( J21,O9:S14,3,FALSE)” (“” shouldn’t be written in the formula bar”).(FOR REFERRING THE CELLS OF THE TABLES, USE THE NEXT PICTURE WHICH SHOWS THE CELL NUMBERS.)
    • Press ENTER. “45” will appear in the cell, as expected.
    • The output screen is shown.

*The step wise step explanation follows after the picture.

EXCEL:VLOOKUP:EXAMPLE:RESULT

STEP WISE STEP EXPLANATION

LET US UNDERSTAND HOW THE FORMULA WAS INTERPRETED BY THE EXCEL.

=vlookup( J21,09:S14,3,FALSE)

WORDWISE BREAKUP OF FORMULA

=EVERY FORMULA STARTS WITH AN “=”
VLOOKUPFUNCTION NAME
J21The value to be found. In this case, ROLL NO. is a unique value which is present in both the tables. With the help of this UNIQUE VALUE we’ll help Excel to search out our desired value and return it to us.
O9:S14Range in which excel will find J21 value. The range is the top left cell upto the bottom right cell of the table.
FALSEExact Match. True is used for Approximate Match

EXAMPLE 2:VLOOKUP IN EXCEL (EXTRACTING MULTIPLE DATA)

WHAT IF WE NEED THE VALUE FOR A LONG LIST.

TO ACHIEVE THIS GOAL , LET US DRAG DOWN THE FORMULA THROUGH THE SAME COLUMN.

EXCEL:VLOOKUP:EXAMPLE:AFTER DRAGGING DOWN THE FORMULA

But we can see that it worked fine for the first 04 rows but not for the last two rows and a #N/A(VALUE NOT AVAILABLE) error showed up.

TO TACKLE SUCH CASE, WHEN WE NEED TO DRAG A VLOOKUP FORMULA
THROUGH A BIG NUMBER OF ROWS, WE NEED TO FIX THE CELL RANGE
AS WHEN WE DRAG, THE CELL RANGE ALSO STARTS CHANGING RELATIVELY.

DO YOU KNOW

DO YOU KNOW ???

EXCEL FORMULAS WORKS RELATIVELY. IF WE DRAG ANY FORMULA, IT’LL CHANGE THE CELL ADDRESSES RELATIVELY

SOLVING THE #N/A PROBLEM IN VLOOKUP

Let us make a change in the formula and put a “$” sign in front of row address and column address of the lookup range. So, the formula become

=vlookup( J21,$O$9:$S$14,3,FALSE)

The change has been highlighted with a RED COLOR.

Now the range has been fixed and it’ll remain the same even if we drag our formula.

Drag the formula again after changing the formula.

EXCEL:VLOOKUP:EXAMPLE AFTER FIXING #N/A ISSUE

REPEAT THE FORMULA FOR ART AND ENGLISH.

Formula in P21 for ART “=VLOOKUP( J21,$O$9:$S$14,4,FALSE)”

Formula in Q21 for ENGLISH= “VLOOKUP( J21,$O$9:$S$14,5,FALSE)”

EXCEL:VLOOKUP:EXAMPLE FINAL RESULT

COLUMN INDEX NUMBER (INPUT NO. 3 IN VLOOKUP FORMULA)

Its very easy to understand. IT IS CONCERNED WITH THE LOOKUP TABLEThe Range, which has been selected by us to find our desired value from, is considered a table by the excel and its columns will be counted from the left to right. Just put a number of the column, the value which you need to retrieve. 

EXAMPLE:

Checkout the illustration given in the picture.

The Range selection area is colored in Green.

The table has four columns but for vlookup, the columns will be counted from the first column of selected range as shown by orange arrows.

EXCEL: VLOOKUP: CONFUSIONS: COLUMN INDEX NUMBER

This was in details introduction to VLOOKUP FUNCTION.Now when you have understanding of the function.