ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

A Step By Step Guide For How To Use Vlookup Function In Excel

Updated on March 29, 2020
hubber8893 profile image

I have 7 years of experience working with excel spreadsheets. I think excel has data analytics capabilities comparable to no other tool.

Excel : An Overview

Microsoft Excel is one of the most widely used computer applications software. It has proven to be immensely helpful when it comes to data management and data analytics. Moreover, it is very compatible with most of system application Softwares. All of us must have been a consumer to this DBMS in our lives. Here we will discuss the use of Vlookup tool available in this spreadsheet software for data analytics and comparison but before we start working on Vlookup we will have to take a look at its complementary function that is "Sort and Filter".

Vlookup
Vlookup | Source

Sort and Filter

The sort function is used for sorting data in a sequential manner so that the analyst could easily point out the outcomes. Similarly, the filtering allows the user to apply a filter on large datasheets for segregating the desired values from undesirable results. Both of these tasks are very helpful in organizing and analyzing large data in a timely manner.

How to use this command in Microsoft Excel?

Just press Alt+D+F+F on your keyboard while the datasheet is open on your PC.

After pressing this command a triangle symbol will appear on the topmost cell of all the columns. When you will click on this triangle symbol it will display many options to sort the data in this particular column from lowest to highest order or vice versa and filter by color, description, etc. as shown in the picture given below.

Sort and Filter in Microsoft Excel
Sort and Filter in Microsoft Excel | Source

VLOOKUP

Vlookup is one of the most powerful and important tools used in excel spreadsheets. This is used to compare the data from one spreadsheet to another one. Unlike other commands, it can also be executed from one excel file to another excel file. The data of one column can be compared to another column in the excel spreadsheet and the common variables are displayed against each other. I will illustrate it by solving the following problem :

Suppose you have an excel spreadsheet containing bill of materials(BOMs) of two distinct car models produced by a car manufacturing Factory. The name of the first model is "AA15" and the second one is "AA18". The sheets contain the part code, descriptions and quantity of components used to make the cars. The company made the model AA15 first and then after a few years they launched another one "AA18". The Engineering design team of both the vehicles says that they have used the design of AA15 as a reference and after doing some modifications in it they created a new model AA18. There are some components which are common in both the AA15 and AA18. Now if a person wants to extract a list of new parts that are used in new model AA18 they will have to compare the BOMs of both models. Since the BOMs are available in excel files so the required list can be prepared with the help of Excel's Vlookup tool. The step by step procedure of doing Vlookup on above-mentioned requirement is as follows:

  • First of all open both the worksheets in Microsoft Excel with BOM "AA18" being displayed at the front end.

Source
  • In front of the first row of BOM "AA18" type the mathematical symbol "=" and then click on text Vlookup(which is shown in circle) in the formula bar as shown in the figure given below.

Source
  • After clicking on the Vlookup function, a dialogue box containing function arguments will appear as shown in the image given below.

Source
  • The functional dialogue box requires 4 arguments to be filled namely Lookup Value, Table Array, Column Index number and Range lookup. The first one is Lookup value which is the data that will be common with the other sheet. In this case, the material codes of BOM AA15 and AA18 will be a connection point between both of them so we will click on the first cell of the material code column. When we will click on this cell its border will start blinking with a dotted line.

Source
  • Now we will press the "tab" key on the keyboard and the cursor will move to the next functional argument that is Table_array. The table array corresponds to the data in the column of the other sheet which is to be used as a reference. In our case, the column "Material code" of BOM "AA15" will be the table array so we will click on the first cell in the Material Code column of the BOM AA15. It will also start blinking like the earlier one but now we will have to select all the cells in this column which have some data because every single value in BOM AA18 will be compared with all the cells in BOM AA15. Therefore we will drag the cursor from the first cell to the last one. If you want to get the data of "Material Description" being displayed as the final outcome then 2 or more columns are required to be selected.

Source
Source
Column selection for Material code outcome
Column selection for Material code outcome | Source
Column selection for Material Description outcome
Column selection for Material Description outcome | Source
  • Again press tab key on keyboard and cursor will return to the dialogue box argument "Column index number". It corresponds to the column number right to the column selected in the table array. It means if you will enter a value of 1 then the result will be the value of the same column "Material Code" from BOM AA15 will be returned, if you will enter value 2 then it will return the value just right to the column Material code(i.e Material description) and so on.

Column Index Number For Material Code As Final Outcome
Column Index Number For Material Code As Final Outcome | Source
Column Index Number For Material Description As Final Outcome
Column Index Number For Material Description As Final Outcome | Source
  • In the last argument of "Range lookup" enter the value 0, press enter and the cell shows the value from BOM AA15 that is matching the corresponding cell in BOM AA18.

Source
Source
  • Select the cell and drag down till the last one.

Source
  • This is how the Vlookup function works, the values matching material code from BOM AA18 with AA15 will be displayed against the same and the ones that do not match will return the value "N/A". There will be different outcomes with Column index value filled as "1" and Column index value filled as "2" as shown in the images given below.

VLookup outcome with column index number  = 1
VLookup outcome with column index number = 1 | Source
VLookup outcome with column index number  = 2
VLookup outcome with column index number = 2 | Source
  • As per our requirement, we want to eliminate the list of new added parts in BOM AA18 so we will have to filter the cells with N/A value. To do this we will press ALT+D+F+F on the keyboard and a triangle like symbol will appear on the topmost cell of each column.

Source
  • Now we will click on the triangle like symbol over the column of Vlookup values, unselect the "select all" box and select the "#N/A" box only.

Source
Source
  • A single list of new added parts will be displayed.

Source

We have practiced the comparison of two sheets with cells having the same number of characters. Sometimes a situation arises such that one cell has 1 or two characters lesser than the other one{e.g. 50004809AA(10 characters) and 50004809(8 characters)}. Vlookup is also helpful in such situations. This can be done as follows :

  • Type the command "=left(" in front of the first cell of BOM AA15.

Source
  • Select the first cell in the column Material code.

Source
  • Press comma"," and type the number of characters that are required to be displayed beginning from the left side and close the bracket. Here we are taking the number "8".

Source
  • Press Enter and drag down the selection till the last row.

Source
  • All the Material codes with an 8 digit value will be displayed as shown in the figure given below.

Source
  • Now we will come back to the first cell in of BOM AA18 and apply the Vlookup function. The Lookup value argument will be filled as "Left(cell number, number of digits required to be compared). We have applied the formula {Left(D5,8)}.

Source
  • To fill the "Table Array" we will select the newly created 8 digit column in BOM AA15.

Source
  • The other two values(Col index num and Range lookup) will remain the same(eg 1,0) and the 8 digit comparable values will be displayed against the corresponding 10 digit material codes".

Source

How likely is the reader to use VLookup function after reading this hub?

See results

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

© 2019 Sourav Rana

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, hubpages.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://maven.io/company/pages/privacy

    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)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)