The Complete Guide to Excel Lookup Formulas
One of the most common tasks in Excel is the process of looking up specific values within a data set. In a simple database, this process would be completed by writing a “query” for a specified “table.” For Excel, you complete this task by using a “formula,” with specific syntax rules, and reference a “data range.” If your intent is to someday become an Excel power user, you will need a solid understanding of all the lookup formulas available to you. Below is list of the most popular Excel lookup formulas with tutorials linked as reference.
VLOOKUP – Vlookup is the oldest and most often used lookup formula in Excel. The first time you write a Vlookup formula is a milestone in your Excel learning. However, after using this formula for a significant amount of time, you will definitely begin to see its flaws.
HLOOKUP – The Horizontal Lookup is essentially a Vlookup transposed; instead of trying to find your lookup values vertically in columns, you are now looking horizontally through rows. The syntaxes of Vlookup and Hlookup are essentially the same; the difference is that with Hlookup you must reference horizontal ranges.
INDEX MATCH – Index Match is the best way to perform a simple vertical lookup. (The second half of this article will explain why) Because the lookup method is actually a combination of two different formulas, the syntax for writing an Index Match can initially be difficult to remember. However, after frequent use, it’s really not a difficult formula to memorize; I currently use Index Match almost exclusively for all of my lookups.
VLOOKUP MATCH – By combining the Vlookup and Match functions of Excel, the originally static column reference within the Vlookup formula becomes dynamic; now whenever you insert a column into your data set, your column reference will update so that you still have the same return value.
VLOOKUP HLOOKUP – When you combine Excel’s basic vertical and horizontal lookup formulas, you end up with a matrix lookup. A matrix lookup implies that you are looking up both a vertical and horizontal value to pinpoint the return value you want to pull.
OFFSET MATCH MATCH – The offset function in Excel is a very versatile formula that, when combined with the Match formula, can replicate the functionality of a lookup. The key difference in using the offset function is that you must start your formula with a single cell reference, typically the top left hand corner of your data range. The Match formula then allows you to move vertically and horizontally from that reference point to find your return value.
INDEX MATCH MATCH – Adding an additional Match formula to the basic Index Match formula enhances it so that you can perform a matrix lookup. It is essentially the same formula as Index Match, except your column reference is now dynamic.
Based on these descriptions, it’s easy to tell that not all lookup formulas are created equal and some are only suited for specific situations. Below is a table comparing the capabilities and benefits of each lookup method.
The first step in deciding which lookup formula to use is to determine what kind of lookup you need to perform. There are essentially three lookup types:
A vertical lookup is the process of defining a lookup value, finding that lookup value vertically on the left hand column of your data set, and then returning the value in the column related to your lookup value.
A horizontal lookup is very similar to a vertical lookup, except that after you define the lookup value, you must find your lookup value horizontally across the top row of your data set, and then return the value in the row related to your lookup value.
Two things to note about horizontal lookups:
- As you can see in the table above, Index Match covers both vertical and horizontal lookups. You can utilize Index Match as an Hlookup formula simply by referencing ranges that are horizontal rather than vertical.
- If you have any familiarity with building databases and understand database theory, you’ll know that setting up data keys horizontally rather than vertically is not a good idea. However, you will likely run into situations where you have to deal with horizontally arranged data, especially in situations involving date keys. Unless you are unable to manipulate the data, one of the best practices in Excel is to copy horizontally arranged data and transpose it. That way you can use a vertical lookup formula rather than a horizontal one.
As mentioned before, a matrix lookup implies that you are looking up both a vertical and horizontal value to pinpoint the return value you want to pull. This is essentially the process of establishing coordinates on a grid to locate a value, except in this case the coordinates are lookup values.
Once you understand the type of lookup you need to perform, there are a number of factors you should to consider to determine which formula works best in your situation.
While definitely not the most important issue, the simplicity of your formula is something you should consider when writing a lookup. While it may be easy for you to memorize and write a complex lookup formula by yourself, the next person who inherits your work may have more difficulty. If you’re only performing a simple lookup and you intend to hand off your work to someone who isn’t spreadsheet proficient, it makes sense to just use the simple Vlookup and Hlookup formulas. For the uninitiated, these formulas are much more intuitive and easier to learn.
Because these formulas are built into Excel, when you begin typing a Vlookup or Hlookup formula, the program prompts you with the required syntax for each input you need to make. Every other formula on the list is a combination of formulas; therefore you have to make custom adjustments to your inputs without Excel to guide you along the way.
Insert Column Immunity
In any complex data analysis, you’re bound to have the need to make changes after you write a lookup formula. Your work is rarely done the minute you finish writing your lookup. This becomes a complication because not all Excel lookup formulas are immune to changes made in your data set.
The most common issue that occurs is when you insert a column (or in the case of Hlookup a row) into your data set. Because the column reference in the basic Vlookup formula is fixed, inserting a column changes your return value. The same issue occurs when you delete a column in your data set.
The basic way to solve this problem is with the Match formula. As you can see in the table above, most formulas that are insertion immune have this component as part of the lookup. Within these lookup formulas, the Match formula acts as a column reference. Because the Match formula returns a value based on the relative position of your lookup, the Match formula makes your column reference dynamic. Therefore, even if you insert a column into your data set, your column reference will automatically update so your return the value stays the same.
Right to Left Lookup
The Vlookup formula requires that your lookup key be on the left hand side of your data set. If you are using this formula, you can’t really make exceptions to this rule. Because the lookup key is on the far left hand side, you can only look up values that are to the right of that lookup key. Therefore, a basic Vlookup is only capable of a “left-to-right” lookup.
The key problem with this limitation occurs when you are trying to create lookup keys for your data set. For example, let’s say you have two fields: first name and last name. You decide that you want to create a new lookup key by concatenating these two fields. To utilize this new key with Vlookup, you must insert the concatenated field to the left hand side of your data set. This process shifts your entire data set to the right and can become problematic if you have other formulas that are referencing this data.
When you are appending lookup keys to a data set, it’s much easier to add them to the right of your data set rather than the left. After appending your lookup keys to the right, the Index Match formulas allow you to perform a “right-to-left” lookup.
Processing need is something people rarely consider when writing lookup formulas. Whenever you write a Vlookup formula that references a large data set, it requires processing power from Excel to calculate the formula. If you’re writing a single formula, you won’t notice a difference between referencing a large data set and a small one. However, if you happen to be building a huge Excel file with thousands of lookup values, processing need becomes a factor.
This is one area where the Index Match and Offset formulas have an advantage. Because these formulas don’t reference the entire data set to perform the lookup calculation, they require less processing power from Excel. Additionally, if you’re using an Excel overlay tool such as Crystal Xcelsius, it makes sense to prioritize lookup formulas that have low processing requirements.
As you may have already figured out from the highlighting in the matrix, the Index Match formulas are the best formulas you can use to perform Excel lookups. Though initially difficult to learn, these formulas provide you optimal lookup functionality while preventing you from making errors in your spreadsheet.