When you desire to pull indevelopment from a table, the Excel VLOOKUP feature is an excellent solution. The capacity to dynamically lookup and retrieve information from a table is a game-changer for many kind of individuals, and you"ll discover VLOOKUP all over.
You are watching: Why is the vlookup function used more often than other lookup functions?
And yet, although VLOOKUP is a relatively easy to use, tbelow is plenty that deserve to go wrong. One factor is that VLOOKUP has a major design flaw — by default, it assumes you"re OK via an approximate enhance. Which you probably aren"t.
This have the right to cause outcomes that look completely normal, also though they are completely incorrect. Trust me, this is NOT something you want to try to explain to your boss, after she"s currently sent your spreadsheet to administration :)
Read below learn how to control this difficulty, and uncover various other tips for mastering the Excel VLOOKUP feature.
1. How VLOOKUP works
VLOOKUP is a role to lookup up and retrieve information in a table. The "V" in VLOOKUP stands for vertical, which implies the information in the table should be arranged vertically, with data in rows. (For horizontally structured information, see HLOOKUP).
If you have actually a well structured table, with indevelopment arranged vertically, and a column on the left which you have the right to use to complement a row, you can probably use VLOOKUP.
VLOOKUP needs that the table be structured so that lookup values show up in the left-most column. The data you want to retrieve (result values) deserve to appear in any type of column to the right. When you usage VLOOKUP, imagine that eextremely column in the table is numbered, starting from the left. To gain a worth from a specific column, sindicate supply the correct number as the "column index". In the instance listed below, we desire to look up the email resolve, so we are using the number 4 for column index:
In the over table, the employee IDs are in column 1 on the left and also the email addresses are in column 4 to the best.
To usage VLOOKUP, you supply 4 pieces of indevelopment, or "arguments":The value you are in search of (lookup_value)The selection of cells that consist of the table (table_array)The variety of the column from which to retrieve an outcome (column_index)The match mode (range_lookup, TRUE = approximate, FALSE = exact)
Video: How to use VLOOKUP (3 min)
If you still don"t gain the fundamental principle of VLOOKUP, Jon Acampora, over at Excel Campus, has actually an excellent explanation based on the Starbucks coffee menu.
2. VLOOKUP just looks right
Perhaps the greatest limitation of VLOOKUP is that it can only look to the ideal to retrieve information.
This indicates that VLOOKUP have the right to just acquire data from columns to the best of initially column in the table. When lookup values appear in the first (leftmost) column, this limitation does not expect a lot, since all other columns are currently to the best. However before, if the lookup column shows up inside the table somewbelow, you"ll just be able to lookup values from columns to the ideal of that column. You"ll also have to supply a smaller table to VLOOKUP that starts via the lookup column.
You can get rid of this limitation by making use of INDEX and MATCH rather of VLOOKUP.
3. VLOOKUP finds the first match
In precise complement mode, if a lookup column has duplicate worths, VLOOKUP will complement the first value only. In the example listed below, we are utilizing VLOOKUP to discover a very first name, and also VLOOKUP is set to perform specific enhance. Although there are two "Janet"s in the list, VLOOKUP matches only the first:
Note: behavior deserve to change when VLOOKUP is supplied in approximate complement mode. This short article describes the topic in information.
4. VLOOKUP is not case-sensitive
When looking up a worth, VLOOKUP does not process top and also reduced case message differently. To VLOOKUP, a product code like "PQRF" is identical to "pqrf". In the example listed below, we are looking for upperinstance "JANET" yet VLOOKUP does not differentiate case so it simply matches "Janet", since that"s the initially enhance it finds:
5. VLOOKUP has actually 2 equivalent modes
VLOOKUP has 2 settings of operation: exact enhance and approximate complement. In many cases, you"ll most likely desire to use VLOOKUP in specific complement mode. This makes feeling as soon as you desire to lookup information based upon a unique vital of some sort, for instance, product information based on a product code, or movie data based upon a movie title:
The formula in H6 to lookup year based upon an accurate enhance of movie title is:
However, you"ll want to usage approximate mode in instances wbelow you"re not matching on a distinct id, yet rather you"re looking up the "best match" or the "finest category". For instance, possibly you"re looking up postage based upon weight, looking up taxation price based on earnings, or looking up a commission rate based on a monthly sales number. In these situations, you likely won"t find the exact lookup value in the table. Instead, you desire VLOOKUP to gain you the best match for a offered lookup worth.
The formula in D5 does an approximate enhance to retrieve the correct commission:
6. Caution: VLOOKUP provides approximate enhance by defaultExact and approximate corresponding in VLOOKUP is managed by the 4th dispute, dubbed "selection lookup". This name is not intuitive, so you"ll just need to memorize how it works.
For specific match, use FALSE or 0. For approximate match, collection range_lookapproximately TRUE or 1:
=VLOOKUP(worth,table,column,TRUE) // approximate match=VLOOKUP(value,table,column,FALSE) // specific match
Unfortunately, the 4th dispute, range_lookup, is optional and defaults to TRUE, which indicates VLOOKUP will carry out an approximate enhance by default. When doing an approximate match, VLOOKUP assumes the table is sorted and also perdevelops a binary search. Throughout a binary search, if VLOOKUP finds an accurate complement value, it returns a worth from that row. If however, VLOOKUP encounters a value better than the lookup value, it will certainly rerevolve a worth from the previous row.
This is a dangerous default because many people unwittingly leave VLOOKUP in it"s default mode, which can cause an incorrect outcome as soon as the table is not sorted.
To prevent this problem, make certain to use FALSE or zero as the fourth debate when you want a precise enhance.
7. You can force VLOOKUP to do an exact match
To pressure VLOOKUP to uncover a precise complement, make sure to set the 4 debate (range_lookup) to FALSE or zero. These two formulas are equivalent:
In precise complement mode, when VLOOKUP can"t discover a value, it will certainly return #N/A. This a clear indication that the value isn"t discovered in the table.
8. You have the right to tell VLOOKUP to perform an approximate match
To usage VLOOKUP in approximate enhance mode, either omit the 4th dispute (range_lookup) or supply it as TRUE or 1. These 3 formulas are equivalent:
We recommend that you constantly set range_lookup dispute explicitly, even though VLOOKUP doesn"t call for it. That means, you constantly have a visual reminder of the match mode you intend.
Video: How to use VLOOKUP for approximate matches
9. For approximate matches, information need to be sorted
If you are making use of approximate-mode equivalent, your data have to be sorted in ascfinishing order by lookup value. Otherwise, you might gain an incorrect outcomes. Also be mindful that sometimes message information might look sorted, also though it"s not.
Felienne Hermans has actually a good instance of this problem below, from a cool evaluation she did on actual Enron spreadsheets!
10. VLOOKUP deserve to merge data in different tables
A common use instance for VLOOKUP is to join information from 2 or even more tables. For instance, probably you have order data in one table, and also customer data in another and you desire to carry some customer data into the order table for analysis:
Due to the fact that the customer id exists in both tables, you can usage this worth to pull in the information you want with VLOOKUP. Just connumber VLOOKUP to usage the id value in table one, and the information in table 2, with the required column index. In the instance listed below, we are using 2 VLOOKUP formulas. One to pull in the customer name, and also the other to pull in the customer state.
Link: Example of merging via VLOOKUP.
Video: How to use VLOOKUP to merge tables.
11. VLOOKUP have the right to classify or categorize data
If you ever before have to use arbitrary categories to data documents, you have the right to easily carry out so with VLOOKUP, by utilizing a table that acts as the "key" to asauthorize categories.
A classic instance is qualities, wbelow you should assign a grade based on a score:
In this case, VLOOKUP is configured for approximate match, so it"s necessary that the table be sorted in ascfinishing order.
But you have the right to likewise use VLOOKUP to asauthorize arbitrary categories. In the example listed below, we are utilizing VLOOKUP to to calculate a team for each department using a small table (called "key") that defines the grouping.
12. Absolute recommendations make VLOOKUP more portable
In instances where you arrangement to retrieve information from even more than one column in a table, or if you need to copy and also paste VLOOKUP, you deserve to conserve time and aggravation by using absolute recommendations for the lookup worth and table variety. This allows you copy the formula, and then change just the column index number to usage the exact same lookup to obtain a worth from a different column.
For example, because the lookup worth and also table range are absolute, we can copy the formula across the columns, then come ago and adjust the column index as essential.
13. Named arrays make VLOOKUP simpler to review (and more portable)
Absolute ranges are pretty ugly looking, so deserve to make your VLOOKUP formulas a lot cleaner and also easier to check out by replacing absolute referrals via named arrays, which are instantly absolute.
For instance, in the employee data example above, you have the right to name the input cell "id" and then name the data in the table "data", you have the right to compose your formula as follows:
Not only is this formula much easier to review, but it"s likewise more portable, because named ranges are instantly absolute.
14. Inserting a column might break existing VLOOKUP formulas
If you have actually existing VLOOKUP formulas in a worksheet, formulas might break if you insert a column in the table. This is bereason hard-coded column index values do not change automatically once columns are put or deleted.
In this instance, the lookups for Rank and also Sales were damaged as soon as a new column was placed between Year and also Rank. Year proceeds to job-related because it is on the left of the placed column:
To avoid this trouble, you have the right to calculate a column index as described in the following two tips.
15. You deserve to usage ROW or COLUMN to calculate a column index
If you"re the form who is bothered by any amount of modifying after copying a formula, you deserve to usage either ROW or COLUMN to geneprice dynamic column indexes. If you"re gaining information from consecutive columns, this trick allows you erected one VLOOKUP formula, then copy it throughout via no transforms forced.
For instance, with the employee data below, we have the right to usage the COLUMN feature to geneprice a dynamic column index. For the initially formula in cell C3, COLUMN by itself will certainly rerotate 3 (bereason column C is 3rd in the worksheet) so we ssuggest need to subtract one, and also copy the formula across:
All formulas are similar through no post-modifying forced.
The formula we are utilizing is this:
16. Use VLOOKUP + MATCH for a fully dynamic column indexTaking the above tip one action further, you deserve to usage MATCH to look up the place of a column in a table and return a totally dynamic column index.
This is occasionally referred to as a two-method lookup given that you are looking up both the row and also the column.
An example would certainly be looking up sales for a salesperchild in a certain month, or looking up the price for a certain product from a particular supplier.
For example, mean you have actually sales per month, damaged out by salesperson:
VLOOKUP deserve to easily uncover the sales person, however it has no means to manage the month name immediately. The trick is to to use the MATCH attribute in area of a static column index.
Notice that we offer enhance a variety that has all columns in the table in order to "sync up" the the column numbers offered by VLOOKUP.
Note: you"ll regularly check out two method lookups done via INDEX and also MATCH, a strategy that supplies more adaptability and much better performance on big information sets. See exactly how in this quick video: How to execute a two-method lookup with INDEX and also MATCH.
17. VLOOKUP permits wildcards for partial matching
Any time you"re using VLOOKUP in specific enhance mode, you have actually the option of using wildcards in the lookup value. It might seem counterintuitive, but wildcards let you do a specific match based upon a partial match :)
Excel provides two wildcard characters: an astedanger (*) matches one or even more characters, and also a question note (?) matches one character.
For example, you can form an astethreat directly into a cell and also describe it as a lookup value via VLOOKUP. In the display screen listed below, we have entered "Mon*" into H3, which is a called range called "val". This causes VLOOKUP to enhance the name "Monet".
The formula in this instance is simple:
If you like, you deserve to change the VLOOKUP formula to use a integrated wildcard, choose the example listed below, wbelow we sindicate concatenate the value in H3 via an astedanger.
In this instance, we are concatenating the astethreat to the lookup worth inside the VLOOKUP function:
Note: Be cautious via wildcards and also VLOOKUP. They give you a basic means to produce a "lazy match", but they likewise make it easy to discover the wrong enhance.
18. You can trap #N/A errors and also display a friendly message
In specific match mode, VLOOKUP will certainly display the #N/A error when no match is uncovered. In one method, this is beneficial bereason it tell you definitively that tright here is no complement in the lookup table. However, #N/A errors aren"t exceptionally fun to look at, so there are numerous methods you deserve to trap this error and display screen somepoint else rather.
Once you begin using VLOOKUP, you"re bound to run right into the #N/A error, which occurs as soon as VLOOKUP isn"t able to discover a match.
This is a useful error, because VLOOKUP is telling you plainly that it can not find the lookup worth. In this example, "Latte" does not exist as a beverage in the table, so VLOOKUP throws an #N/A error
The formula in this instance is a totally traditional precise match:
However before, #N/A errors aren"t incredibly fun to look at, so you can desire to capture this error and screen a much more friendly message.
The simplest way to trap errors through VLOOKUP is to wrap VLOOKUP in the IFERROR function. IFERROR enables you to "catch" any type of error and also rerevolve an outcome of your picking.
To trap this error and also display screen a "not found" message rather of the error, you deserve to simply wrap the orignal formula inside of IFERROR and also set the outcome you want:
If the lookup value is found, no error occurs and also VLOOKUP feature retransforms a normal result. Here is the formula:
19. Numbers as text can reason a match errorSometimes, the table you are working with in VLOOKUP could contain numbers gone into as text. If you are simply retrieving numbers as text from a column in a table, it does not matter. But if the first column of the table contains numbers gotten in as message, you will get an #N/A error if the lookup value is not also text.
In the following instance, the ids for the planet table are numbers gotten in as text, which causes VLOOKUP to return an error considering that the lookup worth is the number 3:
To deal with this trouble, you should make certain the lookup value and also the initially column of the table are both the exact same data type (either both numbers or both text).
One means to execute this is to convert the values in the lookup column to numbers. An simple way to perform this is to add zero utilizing paste unique.
If you don"t have actually straightforward regulate over the resource table, you can likewise change the VLOOKUP formula to transform the lookup value to message by concatenating "" to the worth like so:
If you can"t be specific once you"ll have numbers and as soon as you"ll have text, you have the right to cater to both choices by wrapping VLOOKUP in IFERROR and creating a formula to take care of both cases:
20. You have the right to usage VLOOKUP to rearea nested IF statementsOne of the more amazing uses of VLOOKUP is to rearea nested IF statements. If you"ve ever before built a series of nested IFs, you know that they occupational fine, yet they need a little bit of parentheses wrangling. You likewise have to be cautious about the order you occupational in, so as not to present a logical error.
For example, a prevalent use of nested IFs is to asauthorize qualities based upon a score of some sort. In the instance below, you have the right to view a formula has actually been build through nested IFs to carry out simply that, utilizing the grade vital at the ideal as the guide.
The complete nested IF formula looks prefer this:
This functions fine, yet note that both the logic and the actual scores are baked best into the formula. If the scoring alters for any reason, you"ll need to carefully upday one formula then copy it dvery own the entire table.
By comparison VLOOKUP deserve to assign the exact same qualities with a simple formula. All you should carry out is make sure the grade key table is put up for VLOOKUP (i.e. it the majority of be sorted by score, and also contain brackets to take care of all scores).
After defining a called array "key" for the grade key table, the VLOOKUP formula is incredibly easy and created the exact same qualities as the original nested IFs formula:
With the grade vital table called "key" we have actually a really easy VLOOKUP formula:
A nice bonus of this method is that both the logic and the scores are developed best into the grade vital table. If anything transforms, you have the right to sindicate update the table directly and also the VLOOKUP formulas will upday immediately - no editing compelled.
Video: How to replace nested IFs through VLOOKUP
21. VLOOKUP deserve to just handle a single criteria
By style, VLOOKUP have the right to only discover values based on a solitary criteria, which is provided as a lookup worth to uncover in the initially column of the table (the lookup column).
This indicates you can not conveniently do points favor look up an employee via the last name of "Smith" in "Accounting", or look up an employee based upon first and last names in sepaprice columns.
However before, tbelow are ways get over this limitation. One workaround is to develop a helper column that concatenates values from various columns to create lookup worths that behave favor multiple problems. For instance, below we desire uncover the department and also team for an employee, but the initially name and also last name show up in separate columns. How deserve to we lookup both at once?
First, add a helper column that ssuggest concatenates first and also last names together:
Then configure VLOOKUP to usage a table that consists of this new column, and join first and last names for the lookup value:
The final VLOOKUP formula looks up first and also last names together utilizing the helper column as the key:
22. Two VLOOKUPS are quicker than one VLOOKUPIt may seem completely crazy, but once you have actually a huge collection of information and must do an accurate match, you can rate up VLOOKUP a lot by including another VLOOKUP to the formula!
The background: imagine that you have the majority of order data, say, even more than 10,000 records and you are using VLOOKUP to lookup the order total based on the order id. So, you are using somepoint like this:
The FALSE at the finish pressures VLOOKUP to do a precise enhance. You desire an exact enhance bereason there"s a opportunity that an order number won"t be uncovered. In this case, the exact match establishing will certainly cause VLOOKUP to rerotate #N/A error.
The difficulty is that exact matches are really sluggish, bereason Excel need to proceed in a straight fashion with all values until it finds a match or not.
Conversely, approximate matches are lightning quick bereason Excel is able to perform what"s dubbed a binary search.
The problem via binary searches however (i.e. VLOOKUP in approximate enhance mode) is that VLOOKUP can rerotate the wrong result once a worth isn"t discovered. Worse, the result can look entirely normal, so it have the right to be very difficult to spot.
The solution is to use VLOOKUP twice, both times in approximate match mode. The first instance sindicate checks that the value really exists. If so, an additional VLOOKUP is run (aget, in approximate complement mode) to fetch the information you want. If not, you have the right to return any value you desire to suggest that a result was not uncovered.
The last formula looks choose this:
I learned this approach from Charles Williams of FastExcel, that has actually a great, comprehensive short article here: Why 2 VLOOKUPS are better than 1 VLOOKUP.
Note: Your data must be sorted to usage this trick. It"s simply a means to safeguard against a missing lookup worth, while maintaining a fast lookup.
23. INDEX and also MATCH together deserve to do every little thing VLOOKUP can carry out and more
If you follow Excel digital, you"ll probably run into the VLOOKUP vs. INDEX/MATCH debate. The dispute can get surprisingly heated :)
The gist is this: INDEX + MATCH can execute everything that VLOOKUP (and HLOOKUP) deserve to do, through a lot even more more versatility, at the expense of a bit even more intricacy. So, those in favor of INDEX + MATCH will argue (very sanely) that you can as well start off discovering INDEX and MATCH, because it offers you a far better toolset in the end.
The argument against INDEX + MATCH is that it calls for two attributes instead of one, so it is naturally even more complicated for individuals (especially new users) to learn and also master.
My 2 cents is that if you usage Excel generally, you"re going to want to learn just how to use INDEX and also MATCH. It"s a very powerful combination.
See more: Why Things Are The Way They Are The Way They Are, Why Things Are The Way They Are
But I likewise think you need to learn VLOOKUP, which you"ll run right into everywhere, regularly in worksheets you inherit from others. In straightforward instances, VLOOKUP will acquire the project done just fine through no fuss.