Categories
Design

How to use vlookup in excel

Tip: Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.

Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.

Tip: Check out these YouTube videos from Microsoft Creators for more help with VLOOKUP!

In its simplest form, the VLOOKUP function says:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

How to use vlookup in excel

Tip: The secret to VLOOKUP is to organize your data so that the value you look up (Fruit) is to the left of the return value (Amount) you want to find.

Use the VLOOKUP function to look up a value in a table.

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

The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the table_array argument.

For example, if table-array spans cells B2:D7, then your lookup_value must be in column B.

Lookup_value can be a value or a reference to a cell.

The range of cells in which the VLOOKUP will search for the lookup_value and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references.

The first column in the cell range must contain the lookup_value. The cell range also needs to include the return value you want to find.

The column number (starting with 1 for the left-most column of table_array) that contains the return value.

A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:

Approximate match – 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don’t specify one. For example, =VLOOKUP(90,A1:B100,2,TRUE).

Exact match – 0/FALSE searches for the exact value in the first column. For example, =VLOOKUP(“Smith”,A1:B100,2,FALSE).

How to get started

There are four pieces of information that you will need in order to build the VLOOKUP syntax:

The value you want to look up, also called the lookup value.

The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.

The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.

Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don’t specify anything, the default value will always be TRUE or approximate match.

Now put all of the above together as follows:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

Examples

Here are a few examples of VLOOKUP:

Example 1

How to use vlookup in excel

Example 2

How to use vlookup in excel

Example 3

How to use vlookup in excel

Example 4

How to use vlookup in excel

Example 5

How to use vlookup in excel

You can use VLOOKUP to combine multiple tables into one, as long as one of the tables has fields in common with all the others. This can be especially useful if you need to share a workbook with people who have older versions of Excel that don’t support data features with multiple tables as data sources – by combining the sources into one table and changing the data feature’s data source to the new table, the data feature can be used in older Excel versions (provided the data feature itself is supported by the older version).

Here, columns A-F and H have values or formulas that only use values on the worksheet, and the rest of the columns use VLOOKUP and the values of column A (Client Code) and column B (Attorney) to get data from other tables.

Copy the table that has the common fields onto a new worksheet, and give it a name.

Click Data > Data Tools > Relationships to open the Manage Relationships dialog box.

How to use vlookup in excel

For each listed relationship, note the following:

The field that links the tables (listed in parentheses in the dialog box). This is the lookup_value for your VLOOKUP formula.

The Related Lookup Table name. This is the table_array in your VLOOKUP formula.

The field (column) in the Related Lookup Table that has the data you want in your new column. This information is not shown in the Manage Relationships dialog – you’ll have to look at the Related Lookup Table to see which field you want to retrieve. You want to note the column number (A=1) – this is the col_index_num in your formula.

To add a field to the new table, enter your VLOOKUP formula in the first empty column using the information you gathered in step 3.

In our example, column G uses Attorney (the lookup_value) to get the Bill Rate data from the fourth column ( col_index_num = 4) from the Attorneys worksheet table, tblAttorneys (the table_array), with the formula =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

The formula could also use a cell reference and a range reference. In our example, it would be =VLOOKUP(A2,’Attorneys’!A:D,4,FALSE).

Continue adding fields until you have all the fields that you need. If you are trying to prepare a workbook containing data features that use multiple tables, change the data source of the data feature to the new table.

Data retrieval made simple

How to use vlookup in excel

  • Facebook
  • Twitter
  • LinkedIn
  • Tweet
  • Share
  • Email
  • The VLOOKUP function in Excel is used to find a value in a spreadsheet.
  • The syntax and arguments are =VLOOKUP(search_value, lookup_table, column_number, [approximate_match] )

This article explains how to use the VLOOKUP function in all versions of Excel, including Excel 2019 and Microsoft 365.

What is the VLOOKUP Function?

The VLOOKUP function in Excel is used to find something in a table. If you have rows of data organized by column headings, VLOOKUP can be used to locate a value using the column.

When you do a VLOOKUP, you’re telling Excel to first locate the row that contains the data you want to retrieve, and then to return the value that’s located in a specific column within that row.

How to use vlookup in excel

VLOOKUP Function Syntax & Arguments

There are four possible parts of this function:

=VLOOKUP(search_value, lookup_table, column_number, [approximate_match] )

  • search_value is the value you’re searching for. It must be in the first column of lookup_table.
  • lookup_table is the range you’re searching within. This includes search_value.
  • column_number is the number that represents how many columns into lookup_table, from the left, should be the column that VLOOKUP returns the value from.
  • approximate_match is optional and can be either TRUE or FALSE. It determines whether to find an exact match or an approximate match. When omitted, the default is TRUE, meaning it will find an approximate match.

VLOOKUP Function Examples

Here are some examples showing the VLOOKUP function in action:

Find The Value Next to a Word From a Table

How to use vlookup in excel

This is a simple example of the VLOOKUP function where we need to find how many lemons we have in stock from a list of several items. The range we’re looking through is A2:B5 and the number we need to pull is in column 2 since “In Stock” is the second column from our range. The result here is 22.

Find an Employee’s Number Using Their Name

How to use vlookup in excel

Here are two examples where we write the VLOOKUP function a little differently. They’re both using similar data sets but since we’re pulling information from two separate columns, 3 and 2, we make that distinction at the end of the formula—the first one grabs the position of the person in A8 (Finley) while the second formula returns the name that matches the employee number in A9 (819868). Since the formulas are referencing cells and not a specific text string, we can leave out the quotes.

Use an IF Statement With VLOOKUP

How to use vlookup in excel

VLOOKUP can also be combined with other Excel functions and use data from other sheets. We’re doing both in this example to determine whether we need to order more of the item in Column A. We use the IF function so that if the value in position 2 in Sheet4!A2:B5 is greater than 10, we write No to indicate that we don’t need to order more.

Find The Closest Number In a Table

How to use vlookup in excel

In this final example, we’re using VLOOKUP to locate the discount percentage that should be used for various bulk orders of shoes. The discount we’re searching for is in Column D, the range that includes the discount information is A2:B6, and within that range is column 2 that contains the discount. Since VLOOKUP doesn’t need to find an exact match, approximate_match is left blank to indicate TRUE. If an exact match isn’t found, the function uses the next smaller amount.

You can see that in the first example of 60 orders, the discount isn’t found in the table to the left, so the next smaller amount of 50 is used, which is a 75% discount. Column F is the final price when the discount is figured in.

VLOOKUP Errors & Rules

Here are some things to remember when using the VLOOKUP function in Excel:

  • If search_value is a text string, it must be surrounded in quotes.
  • Excel will return #NO MATCH if VLOOKUP can’t find a result.
  • Excel will return #NO MATCH if there isn’t a number within lookup_table that’s greater or equal to search_value.
  • Excel will return #REF! if column_number is greater than the number of columns in lookup_table.
  • search_value is always in the far left position of lookup_table and is position 1 when determining column_number.
  • If you specify FALSE for approximate_match and no exact match is found, VLOOKUP will return #N/A.
  • If you specify TRUE for approximate_match and no exact match is found, the next smaller value is returned.
  • Unsorted tables should use FALSE for approximate_match so that the first exact match is returned.
  • If approximate_match is TRUE or omitted, the first column needs to be sorted alphabetically or numerically. If it isn’t sorted, Excel might return an unexpected value.
  • Using absolute cell references lets you autofill formulas without changing lookup_table.

Other Functions Like VLOOKUP

VLOOKUP performs vertical lookups, meaning that it retrieves information by counting the columns. If the data is organized horizontally and you want to count down the rows to retrieve the value, you can use the HLOOKUP function.

The XLOOKUP function is similar but works in any direction.

The VLOOKUP function in Microsoft Excel is a great way to find relational data.

For example, you have a busy worksheet with a lot of data points, and you want to know the name of a supplier and the corresponding supplier’s specific phone number, VLOOKUP can come in handy.

There are four necessary pieces of information that you need to know first before you use the VLOOKUP function — including the lookup value, the range of where the value is located, the column number (not letter) it’s in, and whether you want an exact or approximate match.

It seems like a lot, but once you get a handle on the function, it’ll become much easier.

Check out the products mentioned in this article:

Microsoft Office Home (From $149.99 at Microsoft)

Apple Macbook Pro (From $1,299.00 at Apple)

How to use VLOOKUP in Excel

1. Open Microsoft Excel.

2. Open a saved worksheet, or enter your data into a new worksheet before continuing.

3. Select the cell where you want to execute your VLOOKUP formula. It should be blank, so your VLOOKUP result doesn’t overwrite anything. In this example, we’ll use cell C4.

4. At the menu at the top, select “Formulas.”

5. Select “Lookup & Reference.”

6. Scroll down and select VLOOKUP from the dropdown menu.

7. A pop-up screen will then appear on the right where you’ll enter your data fields. First, enter the value you’re looking for. It can be anything from a name, number, or cell location. In this example, we’ll look for pop-singer Lizzo’s Instagram followers — so we would enter “Lizzo” into the text box. The function is case sensitive, so enter the data carefully.

8. Now you will enter where Excel should search for the data — this can be two or more columns, creating a range on the lookup table. Remember: VLOOKUP searches for data vertically, so be sure to enter the column letter followed by the row number. For this example, we want to search through all the data, encompassing all fields on the worksheet, so we’ll enter “A2:B16.”

9. Next, Excel wants you to enter the “column number in the range that contains the return value” — this data point should be where you want the data to come from. In our example, Instagram followers are represented in column B, so we would enter the numerical value of B, which is two (it’s the second column).

10. Finally, enter whether you want the results to be approximate (TRUE) or exact (FALSE). We want our results to be spot on so we will write FALSE. All together our formula looks like this: =VLOOKUP(“Lizzo”,A2:B16,2,FALSE).

11. Select “Done” at the bottom of the formula window.

12. The value will then appear in the cell you selected at the beginning. From our example, we now know Lizzo has 6.4 million Instagram followers without having to manually search the list for it.

The VLOOKUP function in Microsoft Excel is a great way to find relational data.

For example, you have a busy worksheet with a lot of data points, and you want to know the name of a supplier and the corresponding supplier’s specific phone number, VLOOKUP can come in handy.

There are four necessary pieces of information that you need to know first before you use the VLOOKUP function — including the lookup value, the range of where the value is located, the column number (not letter) it’s in, and whether you want an exact or approximate match.

It seems like a lot, but once you get a handle on the function, it’ll become much easier.

Check out the products mentioned in this article:

Microsoft Office Home (From $149.99 at Microsoft)

Apple Macbook Pro (From $1,299.00 at Apple)

How to use VLOOKUP in Excel

1. Open Microsoft Excel.

2. Open a saved worksheet, or enter your data into a new worksheet before continuing.

3. Select the cell where you want to execute your VLOOKUP formula. It should be blank, so your VLOOKUP result doesn’t overwrite anything. In this example, we’ll use cell C4.

4. At the menu at the top, select “Formulas.”

Select “Formulas” from the top menu.

Meira Gebel/Business Insider

5. Select “Lookup & Reference.”

Select “Lookup & Reference.”

Meira Gebel/Business Insider

6. Scroll down and select VLOOKUP from the dropdown menu.

Select “VLOOKUP” from the bottom of the drop-down menu.

Meira Gebel/Business Insider

7. A pop-up screen will then appear on the right where you’ll enter your data fields. First, enter the value you’re looking for. It can be anything from a name, number, or cell location. In this example, we’ll look for pop-singer Lizzo’s Instagram followers — so we would enter “Lizzo” into the text box. The function is case sensitive, so enter the data carefully.

Enter the fields into the formula builder on the right.

Meira Gebel/Business Insider

8. Now you will enter where Excel should search for the data — this can be two or more columns, creating a range on the lookup table. Remember: VLOOKUP searches for data vertically, so be sure to enter the column letter followed by the row number. For this example, we want to search through all the data, encompassing all fields on the worksheet, so we’ll enter “A2:B16.”

9. Next, Excel wants you to enter the “column number in the range that contains the return value” — this data point should be where you want the data to come from. In our example, Instagram followers are represented in column B, so we would enter the numerical value of B, which is two (it’s the second column).

10. Finally, enter whether you want the results to be approximate (TRUE) or exact (FALSE). We want our results to be spot on so we will write FALSE. All together our formula looks like this: =VLOOKUP(“Lizzo”,A2:B16,2,FALSE).

11. Select “Done” at the bottom of the formula window.

Select “Done” once you’ve filled all necessary fields. The result will then appear in the cell you selected.

Meira Gebel/Business Insider

12. The value will then appear in the cell you selected at the beginning. From our example, we now know Lizzo has 6.4 million Instagram followers without having to manually search the list for it.

Insider Inc. receives a commission when you buy through our links.

In this video, we’ll look at how to use VLOOKUP to lookup values in an Excel Table.

On this worksheet, I have a table that contains employee data, named Table1.

To illustrate how to work with VLOOKUP when the source data is in a table, I’ll set up formulas to the right to extract data from the table, matching on an employee ID.

First, I’ll select the table header and use paste special with transpose to get the field values.

Then I’ll add some formatting, and an ID value so I have something to match against.

Now I’ll write the first VLOOKUP formula.

For the lookup, I want the value from K4, locked so it doesn’t change when I copy the formula down.

For table array, I want the lookup table itself, Table1.

Now, because VLOOKUP only looks to the right, it’s important that the lookup is to the left of values we we want to retrieve.

In this table, the ID is the leftmost column, so we can get any value.

For column ID, I need 2, since the first name is the second column in the table.

Match type is zero or false, since I want to force an exact match.

When I enter the formula, we get “Julie”, which is correct.

If I copy the formula down to the next row, I only need to adjust the column number to get last name.

And, I can do the same for all the other fields.

You might wonder if there’s an easy way to avoid hardcoding the column number in the formula?

The answer is yes. Since the names in column J match values in the table header, I can use the MATCH function get an index for each field.

To demonstrate, I’ll use MATCH in column L by itself.

The lookup value comes from column J.

The array is the table header.

Match type is zero, for exact match.

When I copy the formula down, I get a numeric index for each field.

Now I just need to copy MATCH formula into VLOOKUP to replace the hardcoded column index.

This is an example of nesting functions inside a formula.

When I copy the formula down, I get a result for each field.

I’ll go ahead and remove the helper column.

When I change the ID value, everything works.

And, because I’m using a table, I can easily add more data, and the same formulas will continue to work without edits.

Finally, using match this way provides a really nice benefit: I can easily reorder fields in the output formulas, or, in the table itself, and the VLOOKUP formulas keep working.

The VLOOKUP function is used to pull a value from a list or table based on a corresponding value. For example, if you have a worksheet with a table showing employee names, hire date, and salary, you could use VLOOKUP in a separate worksheet to pull the hire date and salary for individual employees from the first worksheet. In this example, the employee name serves as a key, identifying which information from the first worksheet you wish to pull.

This feature works the same in all modern versions of Microsoft Excel: 2010, 2013, and 2016.

To use the VLOOKUP function, you need to know:

  1. Lookup value. The value you will use to identify individual records in your table. The Lookup Value must be in the left-most column of your table.
  2. Table array. The table that contains the data you will use VLOOKUP to retrieve. This table can be in another worksheet or even another workbook from the one in which you enter the VLOOKUP function.
  3. Col index num. The Lookup Value is always in the left-most column of the Table Array (column #1, regardless of where in the worksheet the table is located). The next column to the right is column #2, then column #3, etc. The Col index num is simply the number of the column that contains the value you wish to retrieve.
  4. Range lookup. Enter False if the Lookup Value must match exactly. If you enter True or leave blank, Excel will assume the table is sorted in ascending order and will select the best match. Note that if the table is not sorted in ascending order, Excel likely won’t correctly find the best match.

To use the VLOOKUP function in Excel:

  1. On the Formulas tab, in the Function Library group, click the Insert Function command.
    How to use vlookup in excel
  2. In the Insert Function dialog box:
    1. Search on “VLOOKUP” or, in the Or select a category drop-down box, select Lookup & Reference .
    2. Under Select a function , select VLOOKUP .
    3. Click OK .
      How to use vlookup in excel

  3. In the Function Arguments dialog box:
    1. Enter the Lookup_value or the cell in which it is located.
    2. Enter the Table_array .
    3. Enter the Col_index_num .
    4. Enter the Range_lookup . If you leave this blank, Excel will treat this as if you entered True.
    5. Click OK .
      How to use vlookup in excel

Related Video

Dave Dunn, Webucator’s CEO, joined Webucator in 2009 after serving as a CFO and COO for numerous small and medium-sized companies, including Summit Software Company, Insight Research Group, Avalon Consulting, and HealthcareOne. Dave received his Bachelor of Arts from Hamilton College and his MBA from Syracuse University. He has served as Chairman of the Board of the Montessori School of Syracuse since 2001.

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.

Webucator provides instructor-led online and onsite training. Check out our complete course catalog.

How to use vlookup in excel

What is Excel VLOOKUP & How to Use?

Excel VLOOKUP is an Excel function to lookup and retrieves data from a specific column in the table. VLOOKUP supports approximate and exact matching. The “V” stands for “vertical”. Lookup values must appear in the first column of the table, with lookup columns to the right. you will learn all this in the blog below. To perform this action we use Excel Vlookup Formula.

Today, I have noticed every Professional working with Microsoft Excel using Vlookup formula. What I have experienced while interacting with my workshop participants is that they could tremendously magnify its application power if they learn two associated techniques of VLookup.

Excel Vlookup Formula Techniques

There are two associated techniques of Excel VLookup Formula are:

  1. Adding MATCH() inside VLOOKUP() to convert the one-dimensional Lookup power of VLOOKUP to two-dimensional (Vlookup matches)
  2. Using VLOOKUP() with the “True” parameter to enable you to apply VLOOKUP() where the cell references table may contain numeric values categorized in various Slabs.
    E.g. Debtor’s aging, Scoring Analysis, Year based rental expense escalation, Date based staggered analysis, ascending order & descending order, etc.

I am sharing a quick [infographics] to give an overview. This is from one of the pages of my Excel eBook on “Advanced Excel Training“. All these excel examples are being added to my library of Excel video tutorial as you are reading. leftmost column piece of the information column index
[xyz-ihs snippet=”Excel-Curriculum”]

How to Use Excel Vlookup Formula (Step-By-Step)

Learn Here’s how to use the Excel Vlookup Function step by step

How to use vlookup in excelTutorial syntax:

    1. The value you want to find also called the lookup value.
    2. The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C i.e. called vlookup column.
    3. The column number in the range that contains the return value. For example, if you specify B2: D11 as the range, you should count B as the first column, C as the second, and so on.
    4. Optionally, you can specify TRUE if you want an approximate match or enter FALSE if you want an exact match of the return value. If you don’t specify anything, the default value will always be a TRUE or approximate match.

#YodaHacks – Excel’s VLookup Formulas for Starters:

A brief intro to Excel VLookup example. The VLookup Excel function performs a vertical lookup table by searching for a value in the left-most column of the table and returning the value in the same row in the index number position. i.e. Col_index_num
[xyz-ihs snippet=”Excel-online-Course”]

#YodaHacks – Excel Vlookup Formulas with fixed table arrays:

Continue with an example of data where we fix a table array so that the formula becomes scalable.

Try our FREE Online Excel Course and master essential excel formula and shortcuts in max one hour.

The VLOOKUP function in Microsoft Excel literally means vertical lookup. It’s a search function for querying values in the cell of a column. This function searches for the data relative to the entries in the first column from the left.

A vertical data search is most vital when dealing with tables with numerous columns and rows. Instead of scrolling through and analyzing hundreds of cells, Excel’s VLOOKUP function helps you find the data you’re looking for by looking up the values from top to bottom.

Create, build & use Excel’s VLOOKUP function

In our example, we’ll work with a VLOOKUP function that searches for information about seven employees’ salaries. This section shows you how to use the VLOOKUP function in the following ways:

  1. Write the Excel VLOOKUP function.
  2. Build a VLOOKUP function in Excel.

Without further ado, let’s get to it. In the first method, we’ll create the function manually. Next, we’ll use it from Excel’s inbuilt Functions Arguments wizard.

1] Write the Excel VLOOKUP function

Launch Microsoft Excel and make a column for the values that act as unique identifiers. We’ll call this the reference column.

How to use vlookup in excel

Add some more columns to the right-hand side of the first one you created in the first step and insert values for the cells in these columns. –>

How to use vlookup in excel

Click on an empty cell in the spreadsheet and type in an Employee ID from the reference column of an employee for whom you wish to search for data.

How to use vlookup in excel

Select another empty cell on the spreadsheet in which Excel will store the formula and hence display the returned value. Here, enter the following formula: –>

On entering the above formula, Excel suggests the VLOOKUP syntax:

Arguments or parameters

Here are what the above arguments define in the syntax:

  • lookup_value: the cell with the product identifier from the reference column.
  • table_array: the data range from with to search. It must contain the reference column and the column containing the value you’re looking up. In most cases, you can use the entire worksheet. You can drag your mouse over the values of the table to select a data range.
  • col_index_num: the number of the column from which to look up a value. You put this in from left to right.
  • range_lookup:TRUE for an approximate match, FALSE for an exact match. The value is TRUE by default, but you generally use FALSE.

With this information, we’ll now replace the parameters in the parenthesis with the information we wish to look up. For example, to return Wayne Creed‘s salary, enter the following formula:

How to use vlookup in excel

On navigating away from the cell with the VLOOKUP formula, it returns the value for which you queried. If you get a #N/A error, read this Microsoft guide to learn how to correct it.

2] Build a VLOOKUP function in Excel

The first part showed you how to create a VLOOKUP function manually. If you thought the above method was easy, wait till you read this. Here, you’ll learn how to build a VLOOKUP function quickly using the user-friendly Functions Arguments wizard.

Open Microsoft Excel first, and create a reference column that will contain unique identifiers.

How to use vlookup in excel

Next, create some more columns on the right-hand side of the reference column. Here, we’ll insert the relevant values for the items on the reference column.

How to use vlookup in excel

Select an empty cell and type in a value from the reference cell. This is the value whose properties we’ll lookup.

How to use vlookup in excel

Click on another empty cell. With that selected, click on the Formulas tab.

How to use vlookup in excel

Select the Lookup & Reference tool from the Functions Library and choose VLOOKUP from the dropdown menu. This opens the Functions Arguments wizard.

Fill in the Lookup_value, Table_array, Col_index_num, and Range_lookup fields in the Functions Arguments wizard specified in the first method.

Hit the OK button when you’re done, and the VLOOKUP function will return the results from the arguments you entered.

How to use vlookup in excel

Both methods will successfully query the data you need in reference to the first column. The Formulas Argument wizard makes it easy to input the variables to make the VLOOKUP function work.

This guide was based on Microsoft Excel for Windows 10.

However, the VLOOKUP function also works on the web version of Excel. You also get to use the Functions Argument wizard or create the VLOOKUP function manually on the web version.

How to use vlookup in excel

Before we learn how to use Excel VLOOKUP function, we must learn when do we use Excel VLOOKUP function?

How to use vlookup in excel

Task : Imagine if you have to find when does train no. 6948 departs in the schedule chart above?

  • Your approach: You will scan the first column top to bottom. When you find the same train no., you stop there. Then you scan that row rightwards. Once you reach to the fifth column, you see the answer 23:00.
  • Observation: Please note that train no. 6948 is in the first column and it does not repeat. The train nos. are not sorted either.
    [xyz-ihs snippet=”Excel-Curriculum”]

Definition:

Excel VLOOKUP function helps “pull” value from a table with the help of a matching ID or unique identifier.

In the Example given below, we need to find asset description of three IDs in Table 2. Table 1 has the details of ID wise asset description.

How to use vlookup in excel

You can already see that the ID 7951 refers to Computer Table. That’s because this is a simple example. However, using Excel VLOOKUP function, you can easily find asset descriptions of 1000s of such IDs in less than a minute. This function is very helpful when you have a large amount of data.

I think finding Excel related query and searching on google, not finding the right answer, don’t you think its quite frustating? Its better you enroll in Beginner to Advanced Excel Course & stop for looking for related query.

There are four pieces of information that you will need in order to write the

Excel VLOOKUP function:

How to use vlookup in excel

  • lookup_value: Refer marker “A” in the picture above. First, parameter lookup_value is the unique value or ID Code whose Asset Description is to be found. g. 7951. Some refer such ID code as a primary key.
  • table_array: Refer marker “B” in the picture above. It is the table whose first column must contain the lookup_value or primary key. One of the columns of the table_array will contain details of Asset Description.
  • col_index_num: Refer marker “B” in the picture above. The table_array chosen above has 2 columns. The second column contains Asset Description. Hence, col_index_num is 2.
  • range_lookup: 0 or FALSE indicates an exact match. It means if your primary key in the example above is 7951, then provide asset description only and only if the same key is found in the first column of table_array selected. Else #N/A! an error will be given. We will discuss the situation when to use TRUE or 1 in another blog.

Let’s walk through the process of writing Excel VLOOKUP function like a Pro.
[xyz-ihs snippet=”Excel-online-Course”]

Steps to learn how to use Vlookup Formula in Excel

  • Step 1:Refer VLOOKUP example picturegiven above. Type an = (equal sign) and begin typing “VL” to start an Excel VLOOKUP function. Excel displays a dynamic drop-down list of valid functions starting with the letters VL. Choose the desired function from such list and press TAB key. This will autocomplete the Excel VLOOKUP function =VLOOKUP(

How to use vlookup in excel

  • Step 2: Refer the VLOOKUP example picture given above. Choose the cell F4. It contains the ID code 7951.
    How to use vlookup in excel
  • Step 3: Refer the VLOOKUP example picture given above. Choose the table range where ID codes are given along with the asset description in subsequent columns. The first column of the table_array MUST contain the lookup_value or the primary key.

How to use vlookup in excel[xyz-ihs snippet=”Excel-Curriculum”]

  • Step 4: Refer the VLOOKUP example picture given above. As soon as table_array is chosen, press function key F4 to lock the range selection with four $ signs. This ensures that as the function is copied and pasted below, the range remains B2 :C6 for all. Not doing so may give errors instead of correct answers. Watch the VLOOKUP example picture below. The initial range B2 :C6 has changed to B4 :C8 .

How to use vlookup in excel

A reason for the error (#N/A): As the Excel VLOOKUP function is copied and pasted below, the table_array changes from B2 : C6 to B4 : C8 . This means that the ID code 7082 is being looked up in the revised range. The revised range does not contain the ID code 7082.

How to use vlookup in excel

  • Step 5:Refer the VLOOKUP example picture given above. Now we need to specify the correct column sequence number from the table array. Column number 2 refers to the second column of the chosen table_array. Col_index_num can be 2, 3, 4 … n. It cannot be a negative number or a fraction. E.g. using -1 will not give Serial No. 04.

How to use vlookup in excel

Related readings on: 5 Powerful Tricks to Format cells in Excel

  • Step 6: True (1) and False (0) refers to approximate and exact match, respectively. In the VLOOKUP example picture given above, we want the exact match. E.g. ID 7951 should give the answer “Computer Table”. If the ID 7951 were not present in Table 1, then #N/A will be the answer. It will not look for any close match such as ID 7950 or 7952.

Next Step: Simply drag the cell down using Autofill handlebar. This will help give answers to all the three ID codes.
[xyz-ihs snippet=”Excel-online-Course”]