VLOOKUP in Excel – The Only Guide You Ever need

VLOOKUP is the most known and most used Excel functions in formulas. With thousands of tutorials written on basic to advanced level examples and uses of VLOOKUP in Excel, it is still one of the most asked, learnt and discussed Excel LOOKUP functions ever.

You are here because:

  1. You need Excel VLOOKUP help to learn:
    1. how to do VLOOKUP in excel
    2. understand the syntax and each argument of Excel VLOOKUP function
  2. your VLOOKUP is not working, giving errors and want to know how to fix errors like #NA, #VALUE or #REF then you are on the right page.

What is VLOOKUP

In simple words VLOOKUP is a function that asks Excel to vertically lookup a certain value from a certain range to fetch a certain value.

What is function you asked? By function we mean a command programmed to perform certain task when invoked by the user. VLOOKUP is one of such commands in Excel. Other known commands include IF, COUNTIF, SUMIF etc.

So again in technical words:

VLOOKUP is an Excel function that lookup specified ‘something’ in a user selected range of cells and retrieve/return something from the user specified column that falls in the same row as the value looked-up

Let me break this down for better understanding:

  1. VLOOKUP is an Excel function
  2. that lookup specified something
  3. in a user selected range of cells
  4. and retrieve/return the matching something
  5. from the user specified column
  6. that falls in the same row as the value looked-up
VLOOKUP is an Excel function that lookup specified 'something' in a user selected range of cells and retrieve/return something from the user specified column that falls in the same row as the value looked-upClick To Tweet

Excel for Beginners
1. New to Excel? Start here
2. Learn Excel Interface
3. Excel Shortcuts Mega collection
4. Formatting

Learn Excel by Topic
Charting
Conditional Formatting
Data Validation / Data Tools
Excel Dashboards
Excel Tables
Filter / Slicers / Timeline
Forecast
Form Controls
Formatting
Formulas and Functions
Macros
Pivot Tables
Pivot Charts
Power Pivot
Power Query
Shortcuts
Sparklines
Worksheet Management

Excel Downloads
Ebooks
Templates

Free Excel Guides
VLOOKUP

Before we go any further. Download this practice workbook to help you understand VLOOKUP and its application with a sample data of students in which we would like to lookup stuff using Excel VLOOKUP function.

DOWNLOAD PRACTICE WORKBOOK FOR THIS VLOOKUP GUIDE

VLOOKUP Example from our lives

For almost every beginner VLOOKUP is completely alien word and probably powered to do out-worldly stuff. This is not true. We already know VLOOKUP and perform it quite extensively unknowingly or should I say not with this exact name.

Table of contents of the book

Here a look at this:

Chapter number, chapter name and page number. This topical “data” divided in three columns left to right. To read chapter 7, I look it up in first column and once I find it, I can confirm chapter number in the second column and page number in the third column.

 

So we are doing VLOOKUP ourselves visually and manually not knowing it by this name.

Other examples from our daily lives include:

  1. Telephone directory
  2. Address books
  3. Any list of anything

Understanding VLOOKUP Syntax

Syntax mean a certain way of writing phrases. In Excel and other languages, to execute certain commands we need to learn the correct manner of invoking certain command or function.

For Excel VLOOKUP following syntax is required:

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

Function name is always first on the left in parenthesis and then phrases separated by commas. Each phrase is technically called arguments and takes input from user for a function to work.

For VLOOKUP, there are four arguments i.e. four spaces where user can provide input. The last argument in square bracket is an optional argument. I will discuss in detail later in this tutorial guide.

Remember every function in Excel has its own set of arguments. For the ease of users, Excel does provide a tool tip when a function name is entered.

VLOOKUP arguments explained

Lets understand each part of the function with arguments and what they mean:

VLOOKUP: name of the function.

lookup_value: The value you want to find or lookup. The value you want Excel to lookup is mentioned in this part of the function.

table_array: The range where you want Excel to find the lookup value and fetch the desired result. The data range in which you want Excel to find the lookup value and return the desired value. Yes both finding and fetching will be done from within user specified range. That is why knowing your data and selection is important. More on that in next section.

col_index_num: The column from where you want to fetch the resulting value. User have to mention the relative number of column within the selected data range.

[range_lookup]: The type of match you want Excel to make. Do you want Excel to find an approximate match or Exact match? It is an optional argument and user may select either of two types. For approximate match TRUE is mentioned. And for exact match user has to provide FALSE as argument. If nothing is mentioned, Excel takes approximate match as default and execute the function accordingly. More on this argument later with examples.

VLOOKUP function syntax in plain English: Summary

To sum it all up you must know:

  1. the name of the function correctly. VLOOKUP. Its easy!
  2. the value you want to look up or find.
  3. the data range where you want to find and fetch the value.
  4. the column from which you want Excel to fetch the value.
  5. the type of match you want Excel to perform i.e. Approximate or Exact.

All the points above are dictated by the situation and requirements of the user.

How to use VLOOKUP

Now that we have learnt the syntax of VLOOKUP function, its time to write our first ever VLOOKUP and for that its easy if we divide the whole process in 7 easy steps. Don’t worry 5 of these seven are same as discussed above under syntax:

  1. Get to the appropriate cell and enter edit mode.
  2. write the name of the function correctly. VLOOKUP. Its easy!
  3. know the value you want to look up or find. And mind the reference.
  4. select the data range where you want to find the value. And mind the reference.
  5. mention the column from where you want Excel to fetch the value.
  6. specify the type of match you want Excel to perform i.e. Approximate or Exact or just leave.
  7. Hit Enter! You might feel why mention the obvious? Because there is a thing in the Excel world called array formulas. And they require CTRL+SHIFT+ENTER combo to be pressed. I will come to array formulas when we discuss advanced uses of VLOOKUP later in this guide. So for now hitting Enter is enough 😀

Get to the appropriate cell and enter edit mode

You input the VLOOKUP function where you want the result. As simple as that. For now I am keeping this part as simple as it can get.

Open up the practice workbook you downloaded and if you haven’t downloaded yet then click here. For your ease, I have pre-selected the cells and have labeled them as well. You can see 4 colored set of cells this is where we will be working.

We have four colored columns that I will be using to explain different aspects of VLOOKUP in Excel with different examples.

Factors Affecting Location

Your selection of location depends on many factors like:

  1. you want to return single value or range. If you want to return multiple values in cells then you have to be sure there is enough room vertically below the starting cell to accommodate results.
  2. Is it in the same worksheet or workbook? If not then we will have to refer to appropriate worksheet or workbook in the formula.

Ways to enter edit mode in Excel

To input any data or function in Excel, user has to enter edit mode. Its easy as there are several ways to achieve it:

  1. Reach the desired cell using directional keys or mouse and hit F2 key on the keyboard.
  2. Hover the mouse over desired cell and do double-left-click.
  3. Reach the desired cell and simply start typing. But this replace/overwrite existing content of cell.

As we are starting with fresh cell with no content in it, I can go with simplest method.

To invoke Excel function you always have to start with “=” sign. This preferred specific character in the start of input tells Excel that user wishes to insert Excel function.

So go to the cell I3, it is the second cell in blue column, and press “=” key on the keyboard.

Signs other than = that you can use to start formula in Excel

You can use other signs to start functions as well for example:

  1. -SUM
  2. +IF
  3. @COUNTIF

But “=” is a preferred notation in Excel documentation for formula initiation. And to avoid any confusion it is used as a standard in Excel community. And I would also like you to use this sign to write functions.

Write the name of the function correctly.

Lets move on with our function and input:

=VLOOKUP(

When you punch the “(” Excel will look into VLOOKUP function specifically and you will see the same syntax we discussed above. Basically helping you remember where to input what.

It doesn’t matter if you write the function name in caps or small letters. Excel will accommodate both inputs.

Excel’s handy function suggestion tool tip

The moment you hit “=V” you will notice suggestion list shows up and if you continue typing you will see it getting shortened. And by the time you write whole =VLOOKUP suggestion list shrinks to just one option. You can use this suggestion list to speed things.

If you see your function in the list, like SUM, IF or other then use direction keys on the keyboard to reach it and press TAB key on the keyboard to insert that function in the cell.

DOs and DONTs of writing formula

With helpful Excel tool tips, its hard to make the mistake. However, following pointers must be remembered:

  1. Use the appropriate function initiation sign.
  2. Nothing should precede function initiation sign for function to work.
  3. there are no spaces in formula name.
  4. equal sign is followed by formula name followed by parenthesis. You cannot use braces or square brackets instead of parenthesis

Know the value you want to look up or find. And mind the reference.

Remember how we use the table of contents of any book? To know the page number, we look up for the topic first and then get the page number written infront of it.

VLOOKUP works the same way. That is why it is important that you must know the value you like to look up correctly. If  you look up a wrong topic hoping to read something else, you will not get the right page number.

Now that we have formula initiated, we can mention the lookup value. As our data is related to student and their particulars, our lookup value will be students’ names. We can provide the name directly in the argument or mention the name in some other cell and reference that cell in the function later. I will show you both ways.

What can be a lookup_value

Depending on the data, the lookup value can be:

  1. a text e.g. “Mohammad”
  2. a number e.g. 7
  3. a mix of text and number i.e. alpha-numeric e.g. “Iron56”
  4. a cell address containing the lookup value e.g H7
  5. a mix of text, number and wild card character e.g “Iron5*”
  6. or a concatenation of cell reference and a wild card character e.g. H7&”*”

More on VLOOKUP with wildcards later in this guide.

Hard coded look_value

We can punch in the text or number right in the VLOOKUP function. Providing arguments as such is considered “hard coding” in Excel community and you have to edit the formula every time you like to change the look value.

For text based or alpha-numeric look value you have put in quotes. For numbers however, inverted commas are not required.

This is how formula looks if this method is used:

Cell reference as lookup_value

Instead of hard coding the value, use of cell reference containing the lookup value is considered a better practice but not mandatory for the VLOOKUP to work.

In this case, user selects an appropriate empty cell, mention the lookup value in it and later reference this cell in the VLOOKUP function as lookup_value argument.

Using cell as a reference, however, opens up another topic i.e. understanding cell referencing system.

Most of the time, absolute reference is used for lookup value. But situation can dictate relative or mixed reference to be used.

For now I have the named mentioned in  cell I2 and same is provided as absolute reference in VLOOKUP function:

To change the cell reference from relative to absolute, I hit F4 key on the keyboard.

Once we have the lookup value mentioned, simply hit “,” on the keyboard to complete this argument and move to the second one i.e. table_array. Notice the moment you hit comma key, the second argument gets bold leaving the first argument as normal.

Cell address reference – Absolute, Relative and Mixed

Say there are 50 seats in a classroom. If you want to have your best friend always sitting to your right. Then the reference of his seat is the immediate chair on the right of your chair. Now if you move to any seat in the classroom he will be on your right always because his position is relative to yours or in other words dependent on your location and will update if you move around.

On the other hand if you tell him specifically to sit on the third chair, now you have fixed his position or made it absolute and now if you move to any seat in the class room his position will remain fixed.

Same applies for Excel when you address another cell within in another, a relative reference will update itself if you move from one cell to another. Whereas making an absolute reference will lock it down and it will not change even if you move from one cell to another.

A cell address in Excel is made up of two things; column alphabet and row number. B3 cell address simply means a cell in third row of column B.

Relative cell reference

By default cell reference is always relative in Excel. For example referring to cell D3 from cell B3 simply means two columns to the right in the same row i.e. 3. Therefore, if you have this reference in cell B3 and copy/paste it to F7 the reference will update to H7 which is same two columns to the right in the same row relative to cell F7.

Absolute cell reference

To make this reference absolute we simply need to append “$” signs before column alphabet and row number of cell address. So H7 is relative and $H$7 is absolute. Absolute reference “locks” both column and row as opposed to relative that has both “unlocked”.

To make H7 absolute select cell F7 and hit F2 key on the keyboard or double click with mouse to enter edit mode. Change the address to $H$7 and now if you copy and paste it back to cell B3 it will stick with H7 and won’t change to D3.

Mixed cell reference

A mixed reference on the other hand has either column or row locked. Continuing with our example of D3 referred in B3, $D3 and D$3 are two examples of mixed reference.

In $D3 reference column is absolute but row is relative. In this case if you copy and paste it to cell F7 the reference will update to $D7. As only row was relative therefore only this has changed with column remaining the same.

In D$3 reference column is relative but row is absolute. In this case if copy and paste it to cell F7 the reference will update to H$3 as only column was relative and row was absolute so no change for row number.

Using F4 to switch references fast

Instead of tying the dollar sign yourself, you can have the cursor before, inside or after the cell address while in edit mode and pressing F4 key repeatedly will help you switch the cell reference.

VLOOKUP value with wild cards

Yes! You can use these too.

The data range where you want to find and fetch the value

There are two things we need to consider in this step:

  1. the column in which lookup value be in the data range. I will call it lookup column; and
  2. the column from where you want excel to fetch the desired result. I will call it result column.

Our example data set of students consists of five columns in total. The lookup column is the first one containing names and then we can later decide if we want class room, subject, teacher or contact number which will help us determine which column will be our result column.

Our lookup column will always be the leftmost column in the selected range and result column will always be on the right to lookup column. And this arrangement can never change for VLOOKUP to work correctly. You can never have result columns on the left of lookup column. That is why Excel world knows VLOOKUP always look to the right!

Back to our example, our data range starts from A1 and ends at cell E23 including every cell between these two cells. In Excel this data range is denoted as:

A1:E23

We can manually type it like this or simply use mouse to select this data range by hovering over cell A1 > press and hold left mouse button > drag the mouse till cell E23 > let go left mouse button to complete the selection and Excel will mention this range itself for us:

There are other ways to make the selection mentioned below so learn them and stick with the one that you feel easiest.

Way to select data range: Mouse and keyboard

Data range can be selected using mouse or keyboard.

With mouse you can simply hover above any corner of the range, click and hold the left button and drag the mouse to select the range. Normally we start from top left corner of the range.

With keyboard we have several ways. With an active cell inside the data range one can simply hit CTRL+A combo. If this doesn’t help you can have the active cell at the top left corner of the range, press and hold CTRL+SHIFT keys and use directional keys on keyboard to move, expand contract the selection.

Just like cells, data ranges can also be relative, absolute or mix. In our example having absolute data range is good. Once the range selected, immediately hit F4 key and it will make the address absolute as later you have to select the whole relative address to change it.

Mention the column from where you want Excel to fetch the value

This one is equally important. Once Excel is done with finding appropriate and matching lookup value from the first column of the selected range, you have to tell Excel from which column you want to fetch the result.

You don’t have to worry about the row number as it is always the same row where lookup value is. But you have to give the specific relative number of the column within the selected range.

And I emphasize again, relative column number within selected range. Not according to Excel that is A, B, C and so on, not the heading but as per the selection in previous step. Counting is done starting with the first column in the selected range i.e. lookup column taking it as column 1 and the counting to its right as 2, 3 and so on.

Our example has five columns in total:

So if we want class room, we give column number as 2, if its subject we are after its 3, for teacher its 4 and to get contact number column number to enter in VLOOKUP function its 5.

As we are in a row that has a “Room” mentioned to it, remember we are working in cell I3, so for us relevant column number is 2. So just hit 2 and press comma key.

Now that we have entered the column number, input comma to complete this argument and move on to consider what type of VLOOKUP match-making to use 😉

Specify the type of match you want Excel to perform

This argument is specifically related to lookup_value argument where you give the value to Excel to find in the first column. Here you basically answer the question “Should Excel perform approximate match”. Your answer can be yes (TRUE or 1) or no (FALSE or 0).

False is used if you want Excel to perform the exact match for lookup value in the first column.

True denotes approximate match which is used if data is sorted and you want Excel to find the closest match. There are situations where approximate match works better which I will discuss later in this guide.

To make lookup fail safe, mostly exact match is used and here also exact match will be used. So select False from the drop down appearing on the screen using direction keys and hit Tab to insert False in the function. DO NOT PRESS ENTER to insert match argument. Always use TAB key for this purpose. Enter key is used to execute the function.

Now that we have mentioned the match type, input closing parenthesis.

Hit Enter

Once all the arguments are take care of and function closed with last parenthesis, hit Enter key on the keyboard to execute the VLOOKUP function in Excel.

Finally our TADA moment! We have done our first ever VLOOKUP in Excel!

For many among the readers this step might sound so obvious that it shouldn’t even be a separate step. I can agree but as I have mentioned in the start, we can have array formulas and for that we need to make CTRL+SHIFT+ENTER combo. VLOOKUP in array formulas is discussed below under advanced VLOOKUP techniques.

VLOOKUP example 1: Approximate Match

If you have text in the lookup column, most of the time approximate match does the job without any problem. And you can even skip the optional argument altogether as Excel will default to approximate match. Here is one example:

VLOOKUP Example 2: Problems with Approximate Match

However, this gets serious and completely unreliable pretty fast if you have numbers in the lookup column.

Lets understand it with an example. We have the same data as we used through this guide with one additional column of serial number.

Go to Sheet 2 of practice workbook and in J1 put 2 as we want to return the name of the student with this roll number. Now in cell J2 put the following formula:

=VLOOKUP(J1,A1:F23,2,TRUE)

The name you get is AYAAN which is wrong. Correct name is IMRAN.

Because we are using approximate match, Excel starts from the top and stops right where it finds the nearest match and gets the result from row above it. As our lookup value was “2” it started from top and moment it reached 3 and assumed 2 is absent so returned the result available against serial number “1” which is AYAAN.

To avoid this problem we either need to sort the data or use Exact match type in the VLOOKUP function.

VLOOKUP Example 3: Fixing Approximate Match with Sort

With sorted serial number column results are as following:

VLOOKUP Example 4: Exact Match

However, sorting the data is not always the option therefore, exact match is a better solution as it works even on unsorted lookup column.

Repeating the same with Exact match however corrects the mistake as Excel will consider the whole column first and then return the result without stopping in between. The formula will be:

=VLOOKUP(J1,A1:F23,2,FALSE)

VLOOKUP example 5: Proof – Approximate Match isn’t bad!

So you must be wondering if approximate match is this bad and see no good use of it then why is it even there. Well there are situations where approximate match works extremely good for us saving tons of effort and manual tinkering.

Consider following example where we have grades to assign to students against marks obtained. Grades are based on particular brackets.

A perfect situation where VLOOKUP’s approximate match can help us!

Have your mouse over column I header and right click > insert. This will insert a new column. Make a new heading called Marks and fill the cells as following:

Now go to cell F2 and write the VLOOKUP function with approximate match:

=VLOOKUP(E2,$I$1:$J$7,2,TRUE)

Once the formula is inserted in cell F2 you can double click the fill handle to populate the formula downwards for all the students. Following animation shows all the steps with essential keystrokes

Why your VLOOKUP is not working and how to fix it!

Number of reasons can cause VLOOKUP not to work as intended. We majorly run in two kinds of issues with VLOOKUP:

  1. Vlookup giving errors like:
    1. #N/A
    2. #REF
    3. #VALUE
  2. Vlookup NOT giving errors but its not giving the right result for reasons like:
    1. Wrong lookup value input
    2. Lookup value not referenced correctly
    3. Typos in the input value
    4. Incorrect selection of data
    5. Selected data not referenced correctly
    6. Typos in selected data
    7. Wrong lookup column
    8. Inappropriate match type used

Vlookup giving errors

#N/A

#N/A is short for “Not available” which simply means the value you are looking for is not available. This can happen for several reasons:

The input value argument refers to empty cell.

It can happen by mistake and filling the cell with appropriate value will fix it.

However, it can happen if cell is waiting for input from another cell itself therefore, nothing is in the cell yet causing N/A error. In this case we can use IFERROR function to wrap VLOOKUP function to hide errors if the situation arises. I will explain IFERROR in detail later in this guide.

Here is one example without and with IFERROR in use:

Typos in lookup value i.e. wrongly spelled.

The lookup value provided is spelled wrong and nothing exists for it. Correcting the spellings will get the VLOOKUP back on track.

Typos in the lookup column i.e. the first column of the selected range.

Just like lookup value, if the data is spelled wrongly then using the correct spelling will not yield the desired result as Excel cannot suggest spell correction.

Unwanted spaces or characters in lookup value or lookup column.

This is one of the most common reasons in the industry. As files are exported and imported among software, this can cause data to lose its actual shape and undesired characters added to it which altogether change the nature of data. For example numbers taken as text due to preceding apostrophe in the data. Or spaces at the start or end of cell data.

In this situation data clean techniques are used including:

  1. Text-to-column tool
  2. Find and replace
  3. TRIM function
  4. VALUE function
  5. DATEVALUE function

Check out this tutorial on dealing with special characters like apostrophe using some of the techniques mentioned above.

Incorrect selection of data range resulting in wrong lookup column. Or the lookup column is not the left-most column in the selected data range.

Self explanatory. Lookup column must always be the leftmost column otherwise VLOOKUP will not work correctly.

#REF

#REF error simply means reference is missing and is caused due to:

A column number larger than the number of columns in the selected data range

Following illustration has 5 columns and if I give a number larger than 5, VLOOKUP will give #REF error.

A row or column has been deleted from the selected data range after writing VLOOKUP function. 

However, I was unable to replicate this error in Excel 2016.

#VALUE

This error means value provided in one of the argument is not solvable by the VLOOKUP function.

Input value is longer than 255 characters

VLOOKUP cannot hold more than 255 characters as lookup value. Any bigger than this and you run into #VALUE issues.

Lookup column number is less than 1

It means table array argument either has 0 or a negative number.

#NAME

Function name is spelled wrong.

While typing first indication of having a wrong function spell is that Excel won’t give tool tip suggestions. However, just to replicate the issue which can happen while copying/pasting I have removed one syllable from VLOOKUP and Excel threw #NAME error as following:

VLOOKUP NOT giving errors but not giving the right result either

Wrong lookup value or table array input

Human error, giving a different value than intended, looking in wrong data can all lead to this. Double check that you have given the right lookup value, looking in the correct data and in the right column. VLOOKUP can’t give right results if its fed wrong values.

Lookup value or table array not referenced correctly

This is probably the biggest culprit and goes for both lookup value argument and table array i.e. data range argument. And it is not making the correct reference according to situation.

Situation might require an absolute reference and if you provide relative reference, VLOOKUP will break. Consider following examples.

Say we have a long list of products and for each type we want to get the correct price per unit. VLOOKUP was used but as the formula is dragged it gave errors. Reason is that table array reference wasn’t absolute and as the formula was dragged downwards to populate cells, relative reference caused the table array to move with it thus breaking the VLOOKUP.

It can easily be fixed by updating the formula with table array having absolute reference. Once done we can repopulate the column with updated formula and it will run fine as following:

Real life examples of VLOOKUP function

  1. Excel Dynamic Data Validation Lists – Explained
  2. Stock Ageing Analysis Reports using Excel – How To
  3. FREE Excel Invoice Template V1.0 with Customer and Product list – Unlocked + Download ready!
  4. How to do 2 Way Lookup in Excel – One value to lookup with two criteria using Intersect Operator

Liked the VLOOKUP guide? Pin it!

Share14
Tweet
Pin9K
Share
9K Shares