How to Use the VLookup Function in Microsoft Excel

Updated on July 13, 2018

Using the VLOOKUP function

The VLOOKUP function is an extremely helpful tool in Microsoft Excel. It searches for a value in a table and then returns a corresponding value from the same row.

In the example in this article I have set up a basic data base for a hardware store that sells 6 different colours of paint. By using the VLOOKUP Function, we can type the paint's reference number into Excel and the colour and price of the paint will be returned to us automatically.

How to Use the VLOOKUP function in Microsoft Excel

Source

Syntax

The syntax for the VLOOKUP formula is as follows:

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

This looks confusing to start with, but once you get used to it, it is actually very easy to use.

Let's look at each part as it is used in the hardware store example.

Lookup value

Lookup value

The 'Lookup value' denotes the box containing the data that you want the VLOOKUP function to search for. In my example I want to enter a reference number into box F2 and have the colour of the paint returned in box G2.

I enter "=VLOOKUP" into box G2 and for my Lookup value I enter "F2". This means that Excel knows to look at the value in F2.

We now need to tell Excel what to compare the F2 value to. This is where the next part comes in.

Table

Table

I want Excel to take the reference number I have typed into F2 and find it in the table circled in the picture. To tell Excel to do this I need to type the reference for the table into the second part of the VLOOKUP formula.

If you have given your table a name, you can type this into the formula, but in my example I have just given the range of the table A1:C7.

So far our formula looks like this - =VLOOKUP(F2,A1:C7.

We now need to tell Excel where to find the value that corresponds to our reference number in F2. This is what the index number is for.

Index Number

The third part of the formula is the index number. This tells Excel which column of the table to take the corresponding value from.

I have entered =VLOOKUP(F2,A1:C7,2,... This tells Excel to look for my reference number in the table A1:C7 and then return the value from the same row as my reference number, but in the second column. In this example, Excel has found '10006' in the table and returned the value from the second column, which is 'Pink'.

In box H2, I have entered =VLOOKUP(F2,A1:C7,3, hence Excel has returned the value from the third column, which is the price '10.99'.

Range Lookup

The Range Lookup part of the formula is where you tell Excel how accurate you want the returned data to be. You have two options to enter:

False - Excel will search for exactly what you have typed. If it cannot find an exact match, then it will return 'N/A'.

True - Excel will search for an approximation to what you have typed.

In this example I have put 'false' so if I type 10004, Excel returns 'N/A' as there is no exact match in the table.

If I had used 'true' instead, Excel would pick the next number available below 10004, which in this example is 10002. It would then return the corresponding value for 10002, which is 'blue'.

Final Formula in Full

My full formula in box G2 is =VLOOKUP(F2,A1:C7,2,FALSE).

This takes the input from F2, looks for it in the table A1:C7 and returns the value from the same row as my input, but in the second column (the paint colour column).

In box H2 I have the almost identical formula =VLOOKUP(F2,A1:C7,3,FALSE) so Excel now returns the value from the third column of the table (the price column).

As I have used 'false' in my formula, Excel will return 'N/A' if the exact number I type in cannot be found.

Extra information

The VLOOKUP function is not just confined to within one sheet in your Excel document. You can direct it to look for information within other sheets or even in another Excel document. As long as you enter the reference for the table into the second part of the formula, Excel will find it.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)