Want to know how to use the VLOOKUP function?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch it on YouTube and give it a thumbs-up!

Download the YouTube Excel practice file

 

Watch on YouTube and give it a thumbs up 👍

VLOOKUP Function: Introduction | MyExcelOnline

What does it do?

Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.

Formula breakdown:

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

What it means:

=VLOOKUP(this value, in this list, and get me value in this column, Exact Match/FALSE/0])


Excel`s VLOOKUP function is arguably the most used function in Excel but can also be the most tricky one to understand.  I will show you a VLOOKUP example and in a few steps you will be able to extract values from a table and use them to do your custom reports and analysis.

You will be using VLOOKUP with confidence after this tutorial!

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We need to enter the VLOOKUP function in a blank cell:

=VLOOKUP(

VLOOKUP formula

 

STEP 2: The VLOOKUP arguments:

Lookup_value

What is the value that you want to look for?

In our first example, it will be Laptop, so select the Item name

=VLOOKUP(G15,

VLOOKUP formula

Table_array

What is the table or range that contains your data?

Make sure to select the stock list table so that our VLOOKUP formula will search here

=VLOOKUP(G15, B14:D17,

VLOOKUP formula

Ensure that you press F4 so that you can lock the table range.

=VLOOKUP(G15, $B$14:$D$17,

VLOOKUP formula

Col_index_num

What is the column that you want to retrieve the value from?

Since we want to get the price, our price is on the 2nd column of our source data

=VLOOKUP(G15, $B$14:$D$17, 2,

VLOOKUP formula

Range_lookup

What kind of matching do you need?

We want an exact match of the Laptop text so make sure FALSE is selected.

=VLOOKUP(G15, $B$14:$D$17, 2, FALSE)

VLOOKUP formula

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

VLOOKUP formula

You now have all of the results!

 

How to Use the VLOOKUP Formula in Excel

 

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Leave a Comment

Your email address will not be published. Required fields are marked *

    • Hey Josh,

      Welcome to the MyExcelOnline blog!

      Let me know if you have any questions, I’m here to help 🙂

      Thanks,
      John

    • Hello Helda,

      You can get the following Excel course which goes through all of Excel´s features by clicking on this link: http://www.mothresource.com/193-8.html

      The course takes a beginner/intermediate Excel user and transforms them into an advanced user. It is provided by Mynda over at MyOnlineTrainingHub and she is a GREAT teacher!

      I hope you find this online course helpful.

      Thanks,

      John

    • Hello Rupika,

      With each weekly lessons you will learn lots about Excel if you apply and practice what I teach you to your daily job.

      In no time you too will be an Excel expert & more efficient user!

      Cheers,

      John

  • Asalm-o-alikum,
    You are awesome man, i have learnt a lot from your excel tutorial while browsing youtube.
    It encourage me to subscribe and learn more during online sessions then i visit https://www.myexcelonline.com/pivot-table-course.php
    Here i only get some sample videos regarding Pivot Table. I learnt a lot from them.i desperate want remaining videos kindly help
    me how can i get the remaining videos.

    I have seen velookup lecture but your voice was not there. As your voice was missing it has become difficult to understand.
    Hopping for your reply.

    JazakALLAH.

  • I have downloaded VLOOKUP Function: Introduction and I can see it working but can’t hear it. Do you talk during these?
    I watched you on YouTube, your video on “how to learn pivot tables in 1 hour”. I’m needing to take a test on Excel for a job and pivot tables and Vlookup are crucial to the job and I have no idea how to do them. This video helped so much, Thank You.
    I was thinking if I took your course maybe I wouldn’t have to do Accounts Payable anymore, maybe there are business out there looking for Excel experts. What do you think?
    Thanks so much for being on YouTube!
    Diana

    • Hello Diana,

      With the animated gifs I do not talk in these, I only show the screen and put some written instructions.

      You can have a look at more Vlookup tutorial here: https://www.myexcelonline.com/?s=vlookup

      The key is to practice until you get comfortable with it, so you can go into your job interview with confidence.

      With my Xtreme Pivot Table Course I am sure that after taking this class that you will have a better opportunity to get a promotion or a new job. Analyzing data and making reports out of data is what most finance analysts do and there is huge demand for this kind of role. Pivot Tables help you a lot in this respect.

      I hope this helps.

      Thanks,

      John

  • Question: Copying the formula – when you made a lookup in one field then you just used black cross to copy it to the field below (tablet). When I try do that I copy the value and when I right click it I get 3 auto fill options: copy cells, fill formatting only, fill without formatting). It looks like I cannot choose the option to copy/paste a formula. It is probably some setup somewhere. But where?

    Thanks in advance for your help!

    • Hi Isidora,

      You are using the right mouse button and then dragging the formula down. You need to use the left mouse button to drag the formula down.

      I hope that helps you out.

      Thanks,

      John