How To Use Vlookup In Excel 2010 Step By Step

Today we'll wait at how to use VLOOKUP in Excel with many detailed stride-by-step examples. You'll learn how to Vlookup from another sheet and different workbook, search with wildcards, and a lot more than.

This article begins a serial covering VLOOKUP, one of the most useful Excel functions and at the same fourth dimension ane of the about intricate and to the lowest degree understood. Nosotros volition attempt to explicate the nuts in a very patently linguistic communication to brand the learning bend for an inexperienced user as easy as possible. Nosotros will as well provide formula examples that cover the most typical usages of VLOOKUP in Excel, and try to brand them both informative and fun.

• Excel VLOOKUP – syntax and basic uses
• VLOOKUP formula
• How to practise Vlookup in Excel
• VLOOKUP formula examples
  • Vlookup from some other sheet
  • Vlookup from unlike workbook
  • VLOOKUP formulas with named ranges
• How to use VLOOKUP with wildcards
• Verbal match (Faux) vs. guess lucifer (TRUE)
• Special tools to Vlookup in Excel

Excel VLOOKUP function

What is VLOOKUP? To begin with, it is an Excel part :) What does it do? It searches for the value you specify and returns a matching value from some other column. More technically, the VLOOKUP part looks up a value in the kickoff cavalcade of a given range and returns a value in the same row from another column.

In its common usage, Excel VLOOKUP searches through your data ready based on the unique identifier and brings you lot a piece of information associated with that unique identifier.

The letter "V" stands for "vertical" and is used to differentiate VLOOKUP from the HLOOKUP function that looks up a value in a row rather than cavalcade (H stands for "horizontal").

The part is available in all versions of Excel 365 through Excel 2007.

VLOOKUP syntax

The syntax for the VLOOKUP role is as follows:

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

Where:

• Lookup_value (required) - is the value to search for.
  This tin can exist a value (number, date or text), jail cell reference (reference to a cell containing a lookup value), or the value returned by some other office. Unlike numbers and cell references, text values should ever be in enclosed in "double quotes".
• Table_array (required) - is the range of cells where to search for the lookup value and from which to think a friction match. The VLOOKUP function ever searches in theshowtime column of the tabular array array, which may contain various text values, numbers, dates, and logical values.
• Col_index_num (required) - is the number of the column from which to render a value. The counting starts from the leftmost column in the table array, which is i.
• Range_lookup (optional) - determines whether to search for approximate or exact lucifer:
  • TRUE or omitted (default) - approximate friction match. If an verbal match is not found, the formula searches for the largest value that is smaller than the lookup value. Requires sorting the lookup column in ascending order.
  • FALSE - exact match. The formula searches for a value exactly equal to the lookup value. If an exact lucifer is not found, a #North/A value is returned.

Basic VLOOKUP formula

As an instance, delight have a await at the below formula and try to "translate" it into English:

=VLOOKUP("panthera leo", A2:B6, 2, Imitation)

• The ane^st statement (lookup_value) clearly indicates that the formula looks upward the discussion "lion".
• The two^nd argument (table_array) is A2:B6. Keeping in listen that the search is performed in the left-nearly cavalcade, you lot can read the above formula a lilliputian further: search for "lion" in the range A2:A6. Then far, so good, right?
• The 3^rd argument col_index_num is 2. Significant, we want to return a matching value from column B, which is second in the table array.
• The 4^th argument range_lookup is FALSE, which indicates that we are looking for verbal match.

With all the arguments established, you should accept no trouble reading the whole formula: search for "panthera leo" in A2:A6, find an verbal match, and render a value from column B in the same row.

For the sake of convenience, you lot can blazon the value of involvement in some cell, say E1, supersede the "hardcoded" text with the cell reference, and go the formula to look up any value you input in E1:

=VLOOKUP(E1, A2:B6, 2, FALSE)

Does anything remain unclear? So endeavor looking at it this style:

How to practise a Vlookup in Excel

When using VLOOKUP formulas in existent-life worksheets, the chief rule of thumb is this: lock table assortment with absolute cell references (similar $A$2:$C$11) to prevent it from irresolute when copying a formula to other cells.

The lookup value in almost cases should be a relative reference (like E2) or yous can lock only the cavalcade coordinate ($E2). When the formula gets copied down the cavalcade, the reference will adjust automatically for each row.

To encounter how it works in practice, please consider the post-obit example. To our sample table, nosotros take added one more than column that ranks the animals by speed (column A) and want to discover the 1^st, v^th and 10^th fastest sprinter in the world. For this, enter the lookup ranks in some cells (E2:E4 in the screenshot below), and use the post-obit formulas:

To pull the beast names from cavalcade B:

=VLOOKUP($E2, $A$two:$C$11, 2, Imitation)

To extract speed from cavalcade C:

=VLOOKUP($E2, $A$2:$C$11, 3, Imitation)

Enter the above formulas in cells F2 and G2, select those cells, and drag the formulas to the below rows:

If y'all investigate the formula in a lower row, you volition notice that the lookup value reference has adjusted for that specific row, while the table array is unchanged:

Beneath, y'all will take a few more than useful tips that will save y'all a lot of headache and troubleshooting time.

Excel VLOOKUP - 5 things to remember!

1. The VLOOKUP function cannot look at its left. It always searches in the leftmost column of the table array and returns a value from a cavalcade to the correct. If you need to pull values from left, apply the Alphabetize MATCH combination that tin can does not care most the positioning of the lookup and return columns.
2. The VLOOKUP part is case-insensitive, meaning that uppercase and lowercase characters are treated equally equivalent. To distinguish the alphabetic character case, use example sensitive VLOOKUP formulas.
3. Remember about the importance of the concluding parameter. Utilize Truthful for guess match and FALSE for exact match. For full details, please see VLOOKUP TRUE vs. Simulated.
4. When searching for approximate match, make certain the data in the lookup column is sorted in ascending society.
5. If the lookup value is not plant, a #N/A error is returned. For information about other errors, please meet Why Excel VLOOKUP is not working.

Excel VLOOKUP examples

I hope vertical lookup is starting to await a scrap more than familiar to you. To strengthen your noesis, let's build a few more VLOOKUP formulas.

How to Vlookup from some other sheet in Excel

In practice, the Excel VLOOKUP office is rarely used with data in the same worksheet. Virtually oftentimes yous volition take to pull matching data from a unlike worksheet.

To Vlookup from a different Excel sheet, put the worksheet's name followed by an exclamation mark in the table_array argument before the range reference. For example, to search in the range A2:B10 on Sheet2, use this formula:

=VLOOKUP("Product1", Sheet2!A2:B10, 2)

Of course, yous don't accept to type the sheet'south name manually. Simply, get-go typing the formula and when it comes to the table_array argument, switch to the lookup worksheet and select the range using the mouse.

For instance, this is how you lot tin can look upward the A2 value in the range A2:A9 on the Prices worksheet and return a matching value from column C:

=VLOOKUP(A2, Prices!$A$2:$C$9, iii, FALSE)

Notes:

• If the spreadsheet name contains spaces or non-alphabetical characters, information technology must be enclosed in unmarried quotation marks, e.g. 'Toll list'!$A$two:$C$9.
• In case you lot use a VLOOKUP formula for multiple cells, recall to lock table_array with the $ sign, like $A$ii:$C$9.

How to Vlookup from another workbook in Excel

To Vlookup from a unlike Excel workbook, put the workbook'south proper name enclosed in square brackets before the worksheet'south proper noun.

For example, hither's the formula to look upward the A2 value on the sheet named Prices in the Price_List.xlsx workbook:

=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)

If either a workbook name or worksheet name contains spaces or non-alphabetical characters, you should enclose them in single quotes similar this:

=VLOOKUP(A2, '[Price List.xlsx]Prices'!$A$2:$C$ix, 3, Imitation)

The easiest way to brand a VLOOKUP formula that refers to a dissimilar workbook is this:

1. Open both files.
2. Showtime typing your formula, switch to the other workbook, and select the table assortment using the mouse.
3. Enter the remaining arguments and press the Enter key to complete your formula.

The result volition look somewhat like the screenshot below:

In one case you close the file with your lookup table, the VLOOKUP formula will continue working, but information technology volition now brandish the full path for the airtight workbook:

For more information, please see How to refer to another Excel sail or workbook.

How to Vlookup from a named range in some other sheet

In case you lot plan to use the same lookup range in many formulas, you can create a named range for it and type the name directly in the table_array argument.

To create a named range, simply select the cells and blazon the name you want in the Name box to the left of the Formula bar. For the detailed steps, please see How to name a range in Excel.

For this example, we gave the name Prices_2020 to the information cells (A2:C9) in the lookup sheet and go this compact formula:

=VLOOKUP(A2, Prices_2020, three, FALSE)

Nearly names in Excel apply to the entire workbook, so yous don't need to specify the worksheet'due south proper noun when using named ranges.

If the named range is in another workbook, put the workbook's name earlier the range proper name, for example:

=VLOOKUP(A2, 'Price List.xlsx'!Prices_2020, 3, FALSE)

Such formulas are far more understandable, aren't they? Besides, using named ranges can be a practiced alternative to absolute references. Since a named range doesn't change, you lot can exist sure that your table array volition remain locked no matter where the formula is moved or copied.

If y'all have converted your lookup range into a fully-functional Excel tabular array, so yous tin can do a Vlookup based on the tabular array name, due east.g. Price_table in the below formula:

=VLOOKUP(A2, Price_table, 3, Simulated)

Table references, also called structured references, are resilient and immune to many information manipulations. For instance, you can remove or add together new rows to your lookup table without worrying about updating the references.

Using wildcards in VLOOKUP formula

Like many other formulas, the Excel VLOOKUP part accepts the following wildcard characters:

• Question mark (?) to match any single character.
• Asterisk (*) to match any sequence of characters.

Wildcards prove really useful in many situations:

• When you do non remember the exact text you are looking for.
• When you are looking for a text string that is part of the jail cell contents.
• When a lookup cavalcade contains leading or trailing spaces. In that instance, yous may rack your brain trying to effigy out why a normal formula does not work.

Example 1. Look up text starting or catastrophe with sure characters

Suppose you lot want to discover a certain customer in the below database. Y'all do not retrieve the surname, but you are confident that it starts with "ack".

To return the last name from column A, employ the following Vlookup wildcard formula:

=VLOOKUP("ack*", $A$2:$B$x, 1, Simulated)

To recollect the license cardinal from column B, use this one (the departure is but in the column index number):

=VLOOKUP("ack*", $A$2:$B$10, 2, Imitation)

You can too enter the known part of the name in some cell, say E1, and combine the wildcard character with the prison cell reference:

=VLOOKUP(E1&"*", $A$ii:$B$ten, 1, FALSE)

The below screenshot shows the results:

Beneath are a few more VLOOKUP formulas with wildcards.

Find the last proper name ending with "son":

=VLOOKUP("*son", $A$two:$B$x, 1, FALSE)

Get the proper noun that starts with "joh" and ends with "son":

=VLOOKUP("joh*son", $A$2:$B$x, 1, FALSE)

Pull a 5-character last name:

=VLOOKUP("?????", $A$2:$B$10, 1, FALSE)

Example 2. VLOOKUP wildcard based on jail cell value

From the previous example, you already know that information technology is possible to concatenate an ampersand (&) and a cell reference to make a lookup cord. To find a value that contains a given graphic symbol(s) in whatever position, put an ampersand earlier and after the cell reference.

Let's say, you wish to go a proper noun corresponding to a certain license central, but you don't know the whole key, only a few characters. With the keys in column A, names in column B, and part of the target key in E1, you tin do a wildcard Vlookup in this way:

Extract the key:

=VLOOKUP("*"&E1&"*", $A$2:$B$ten, one, FALSE)

Extract the name:

=VLOOKUP("*"&E1&"*", $A$2:$B$10, 2, Imitation)

Notes:

• For a wildcard VLOOKUP formula to work correctly, apply an verbal friction match (Faux is the terminal argument).
• If more than one match is institute, the first one is returned.

VLOOKUP True vs False

And now, it'south time to take a closer look at the final argument of the Excel VLOOKUP part. Though optional, the range_lookup parameter is highly of import. Depending on whether you choose TRUE or Simulated, your formula may yield different results.

Excel VLOOKUP exact match (FALSE)

If range_lookup is fix to Faux, a Vlookup formula searches for a value that is exactly equal to the lookup value. If two or more matches are institute, the 1st ane is returned. If an exact match is not found, the #N/A error occurs.

Excel VLOOKUP approximate friction match (True)

If range_lookup is set up to TRUE or omitted (default), the formula looks up the closest match. More precisely, it searches for an exact match starting time, and if an exact lucifer is not found, looks for the next largest value that is less than the lookup value.

An estimate match Vlookup works with the following caveats:

• The lookup cavalcade must be sorted in ascending society, from smallest to largest, otherwise a correct value may not be found.
• If the lookup value is smaller than the smallest value in the lookup assortment, a #N/A error is returned.

The following examples will aid y'all improve understand the deviation betwixt an verbal match and approximate lucifer Vlookup and when each formula is best to be used.

Example 1. How to practise an verbal match Vlookup

To look up an exact lucifer, simply put FALSE in the concluding argument.

For this example, allow's take the animate being speed table, swap the columns, and try to find the animals that can run 80, 50 and thirty miles per 60 minutes. With the lookup values in D2, D3 and D4, enter the below formula in E2, and then copy it down to 2 more cells:

=VLOOKUP(D2, $A$ii:$B$12, ii, Faux)

Every bit you lot tin meet, the formula returns "Lion" in E3 because they run exactly l per hour. For the other two lookup values an verbal friction match is not found, and #North/A errors appear.

Example ii. How to Vlookup for approximate match

To wait upwards an estimate lucifer, in that location are two essential things you need to exercise:

• Sort the first column of table_array from smallest to largest.
• Use TRUE for the range_lookup argument or omit it.

Sorting the lookup cavalcade is very important considering the VLOOKUP function stops searching as before long as it finds a close match smaller than the lookup value. If the data is not sorted properly, you may end upward having really strange results or a bunch of #N/A errors.

For our sample data, an approximate match Vlookup formula goes as follows:

=VLOOKUP(D2, $A$2:$B$12, 2, TRUE)

And returns the following results:

• For a lookup value of "80", "Cheetah" is returned considering its speed (70) is the closest lucifer that is smaller than the lookup value.
• For a lookup value of "50", an exact friction match is returned (Lion).
• For a lookup value of "30", a #N/A fault is returned considering the lookup value is less than the smallest value in the lookup cavalcade.

Undoubtedly, VLOOKUP is i of the nigh powerful and useful Excel functions, but it'southward besides one of the near confusing ones. To brand the learning curve less steep and feel more enjoyable, we included a couple of time-saving tools in our Ultimate Suite for Excel.

VLOOKUP Wizard - easy way to write complex formulas

The interactive VLOOKUP Magician will walk you through the configuration options to build a perfect formula for the criteria you specify. Depending on your information structure, it will utilize the standard VLOOKUP role or an INDEX MATCH formula that can pull values from left.

To become your custom-tailored formula, this is what you need to do:

1. Run the VLOOKUP Wizard.

1. Choose your principal table and lookup tabular array.
2. Specify the post-obit columns (in many cases they are picked automatically):
   • Key column - the cavalcade in your main table containing the values to look up.
   • Lookup column - the column to look up against.
   • Return column - the cavalcade from which to retrieve values.
3. Click the Insert button.

The following examples testify the wizard in activity.

Standard Vlookup

When the lookup column (Animal) is the leftmost column in the lookup table, a normal VLOOKUP formula for exact lucifer is inserted:

Vlookup to the left

When the lookup column (Animal) is on the right side of the return column (Speed), the wizard inserts an INDEX MATCH formula to Vlookup right to left:

Extra bonus! Due to the clever employ of cells references, the formulas tin exist copied or moved to whatever column, without y'all having to update the references.

Merge Two Tables - formula-free alternative to Excel VLOOKUP

If your Excel files are enormously large and circuitous, the project's deadline is imminent, and you are looking for someone who can lend yous a helping hand, try out the Merge Tables Sorcerer.

This tool is our visual and stress-complimentary culling to Excel'south VLOOKUP function, which works this way:

1. Select your main tabular array.
2. Select the lookup table.
3. Cull ane or several common columns every bit the unique identifier(s).
4. Specify which columns to update.
5. Optionally, choose the columns to add together.
6. Permit the Merge Tables Magician a few seconds for processing… and savour the results :)

That'due south how to utilize VLOOKUP in Excel at the basic level. In the next part of our tutorial, we will talk over advanced VLOOKUP examples that will teach you how to Vlookup multiple criteria, return all matches or Nth occurrence, look upwardly across multiple sheets with a single formula, and more than. I thanks for reading and hope to come across yous next week!

Available downloads

Excel VLOOKUP formula examples (.xlsx file)
Ultimate Suite 14-day fully-functional version (.null file)

You may also be interested in

Source: https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/

Posted by: shawsmandertne.blogspot.com

Share this post