How to Use VLOOKUP Function in Microsoft Excel [+ Video Tutorial]

Coordinating a large quantity of knowledge in Microsoft Excel is a time-consuming headache. Fortunately, you do not have to. The VLOOKUP operate may also help you automate this process and prevent tons of time.

What does VLOOKUP do, precisely? This is the easy clarification: The VLOOKUP operate searches for a particular worth in your information, and as soon as it identifies that worth, it may possibly discover — and show — another piece of knowledge that is related to that worth.

Microsoft Excel’s VLOOKUP operate is less complicated to make use of than you assume. What’s extra, it’s extremely highly effective, and is certainly one thing you wish to have in your arsenal of analytical weapons.

Skip to:

How does VLOOKUP work?

VLOOKUP stands for “vertical lookup.” In Excel, this implies the act of wanting up information vertically throughout a spreadsheet, utilizing the spreadsheet’s columns — and a novel identifier inside these columns — as the idea of your search. Whenever you lookup your information, it have to be listed vertically wherever that information is situated.

VLOOKUP Excel Components 

Microsoft describes the VLOOKUP formulation or operate as follows:

=VLOOKUP(lookup worth, vary containing the lookup worth, the column quantity within the vary containing the return worth, Approximate match (TRUE) or Actual match (FALSE)).

It helps to prepare your information in a approach in order that the worth you wish to lookup is to the left of the return worth you wish to discover.

The formulation all the time searches to the suitable.

When conducting a VLOOKUP in Excel, you are primarily searching for new information in a unique spreadsheet that’s related to previous information in your present one. When VLOOKUP runs this search, it all the time appears for the brand new information to the proper of your present information.

For example, if one spreadsheet has a vertical checklist of names, and one other spreadsheet has an unorganized checklist of these names and their e mail addresses, you need to use VLOOKUP to retrieve these e mail addresses within the order you may have them in your first spreadsheet. These e mail addresses have to be listed within the column to the suitable of the names within the second spreadsheet, or Excel will not be capable to discover them. (Go determine … )

The formulation wants a novel identifier to retrieve information.

The key to how VLOOKUP works? Distinctive identifiers.

A novel identifier is a bit of knowledge that each of your information sources share, and — as its title implies — it’s distinctive (i.e. the identifier is barely related to one report in your database). Distinctive identifiers embrace product codes, stock-keeping models (SKUs), and buyer contacts.

Alright, sufficient clarification: let’s have a look at one other instance of the VLOOKUP in motion!

VLOOKUP Excel Instance

Within the video under, we’ll present an instance in motion, utilizing the VLOOKUP operate to match e mail addresses (from a second information supply) to their corresponding information in a separate sheet. 

Creator’s notice: There are various completely different variations of Excel, so what you see within the video above won’t all the time match up precisely with what you may see in your model. That is why we encourage you to comply with together with the written directions under.

To your reference, this is what the syntax for a VLOOKUP operate appears like:

VLOOKUP(lookup_value , table_array , col_index_num , range_lookup)

Within the steps under, we’ll assign the suitable worth to every of those parts, utilizing buyer names as our distinctive identifier to search out the MRR of every buyer.

1. Determine a column of cells you’d wish to fill with new information.

Bear in mind, you are trying to retrieve information from one other sheet and deposit it into this one. With that in thoughts, label a column subsequent to the cells you need extra info on with a correct title within the high cell, similar to “MRR,” for month-to-month recurring income. This new column is the place the info you are fetching will go.

2. Choose ‘Operate’ (Fx) > VLOOKUP and insert this formulation into your highlighted cell.

To the left of the textual content bar above your spreadsheet, you may see a small operate icon that appears like a script: Fx. Click on on the primary empty cell beneath your column title after which click on this operate icon. A field titled Components Builder or Insert Operate will seem to the suitable of your display (relying on which model of Excel you may have).

Seek for and choose “VLOOKUP” from the checklist of choices included within the Components Builder. Then, choose OK or Insert Operate to begin constructing your VLOOKUP. The cell you at present have highlighted in your spreadsheet ought to now appear to be this: “=VLOOKUP()“

It’s also possible to enter this formulation right into a name manually by getting into the daring textual content above precisely into your required cell.

With the =VLOOKUP textual content entered into your first cell, it is time to fill the formulation with 4 completely different standards. These standards will assist Excel slender down precisely the place the info you need is situated and what to search for.

3. Enter the lookup worth for which you wish to retrieve new information.

The primary standards is your lookup worth — that is the worth of your spreadsheet that has information related to it, which you need Excel to search out and return for you. To enter it, click on on the cell that carries a worth you are looking for a match for. In our instance, proven above, it is in cell A2. You will begin migrating your new information into D2, since this cell represents the MRR of the client title listed in A2.

Take into accout your lookup worth might be something: textual content, numbers, web site hyperlinks, you title it. So long as the worth you are wanting up matches the worth within the referring spreadsheet — which we’ll discuss that within the subsequent step — this operate will return the info you need.

4. Enter the desk array of the spreadsheet the place your required information is situated.

Subsequent to the “desk array” subject, enter the vary of cells you need to look and the sheet the place these cells are situated, utilizing the format proven within the screenshot above. The entry above means the info we’re searching for is in a spreadsheet titled “Pages” and might be discovered wherever between column B and column Ok.

The sheet the place your information is situated have to be inside your present Excel file. This implies your information can both be in a unique desk of cells someplace in your present spreadsheet, or in a unique spreadsheet linked on the backside of your workbook, as proven under.

For instance, in case your information is situated in “Sheet2” between cells C7 and L18, your desk array entry will probably be “Sheet2!C7:L18.”

5. Enter the column variety of the info you need Excel to return.

Beneath the desk array subject, you may enter the “column index quantity” of the desk array you are looking out by means of. For instance, if you happen to’re specializing in columns B by means of Ok (notated “B:Ok” when entered within the “desk array” subject), however the particular values you need are in column Ok, you may enter “10” within the “column index quantity” subject, since column Ok is the tenth column from the left.

6. Enter your vary lookup to search out a precise or approximate match of your lookup worth.

In conditions like ours, which considerations month-to-month income, you wish to discover actual matches from the desk you are looking out by means of. To do that, enter “FALSE” within the “vary lookup” subject. This tells Excel you wish to discover solely the precise income related to every gross sales contact.

To reply your burning query: Sure, you possibly can permit Excel to search for an approximate match as a substitute of a precise match. To take action, merely enter TRUE as a substitute of FALSE within the fourth subject proven above.

When VLOOKUP is ready for an approximate match, it is searching for information that the majority carefully resembles your lookup worth, slightly than information that’s similar to that worth. In the event you’re wanting up information related to a listing of web site hyperlinks, for instance, and a few of your hyperlinks have “https://” firstly, it would behoove you to search out an approximate match simply in case there are hyperlinks that wouldn’t have this “https://” tag. This fashion, the remainder of the hyperlink can match with out this preliminary textual content tag inflicting your VLOOKUP formulation to return an error if Excel cannot discover it.

7. Click on ‘Achieved’ (or ‘Enter’) and fill your new column.

As a way to formally carry within the values you need into your new column from Step 1, click on “Achieved” (or “Enter,” relying in your model of Excel) after filling the “vary lookup” subject. This may populate your first cell. You may take this chance to look within the different spreadsheet to ensure this was the right worth.

If that’s the case, populate the remainder of the brand new column with every subsequent worth by clicking the primary stuffed cell, then clicking the tiny sq. that seems on the bottom-right nook of this cell. Achieved! All of your values ought to seem.

VLOOKUP Not Working?

VLOOKUP Tutorial

Acquired caught after attempting to conduct your individual VLOOKUP with the steps above? Try this helpful tutorial from Microsoft has a helpful tutorial that can stroll you thru correctly utilizing the operate. 

Picture Supply

In the event you’ve adopted the above steps and your VLOOKUP continues to be not working, it can both be a difficulty along with your:

• Syntax (i.e. how you have structured the formulation)
• Values (i.e. whether or not the info it is wanting up is sweet and formatted accurately)

Troubleshooting VLOOKUP Syntax

Begin with wanting on the VLOOKUP formulation that you’ve got written within the designated cell.

• Is it referring to the suitable lookup worth for its key identifier?
• Does it specify the right desk array vary for the values it must retrieve
• Does it specify the right sheet for the vary?
• Is that sheet spelled accurately?
• Is it utilizing the right syntax to seek advice from the sheet? (e.g. Pages!B:Ok or ‘Sheet 1’!B:Ok)
• Has the right column quantity been specified? (e.g. A is 1, B is 2, and so forth)
• Is True or False the right route for a way your sheet is ready up?

Troubleshooting VLOOKUP Values

If the syntax just isn’t the issue, how you might have a difficulty with the values you are attempting to obtain themselves. This typically manifests as an #N/A error the place the VLOOKUP can not discover a referenced worth.

• Are the values formatted vertically and from proper to left?
• Do the values match the way you seek advice from them?

For instance, if you happen to’re wanting up URL information, every URL have to be a row with its corresponding information to the left of it in the identical row. When you have the URLs as column headers with the info shifting vertically, the VLOOKUP won’t work.

Holding with this instance, the URLs should match in format in each sheets. When you have one sheet together with the “https://” within the worth whereas the opposite sheet omits the “https://”, the VLOOKUP won’t be able to match the values.

VLOOKUPs as a Highly effective Advertising Device

Entrepreneurs have to research information from a wide range of sources to get an entire image of lead technology (and extra). Microsoft Excel is the right instrument to do that precisely and at scale, particularly with the VLOOKUP operate.

Editor’s notice: This submit was initially printed in March 2019 and has been up to date for comprehensiveness.