Tutorial: How to Decide Which Excel Lookup Formula to Use

by Matthew Kuo on September 24, 2012

in Database Theory, Excel

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.

Click here for a tutorial on VLOOKUP

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.

Click here for a tutorial on HLOOKUP

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.

Click here for a tutorial on INDEX MATCH

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.

Click here for a tutorial on VLOOKUP MATCH

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.

Click here for a tutorial on INDEX MATCH MATCH

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:

Vertical Lookup

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.

Horizontal Lookup

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:

  1. 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.
  2. 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.

Matrix Lookup

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.

Simplicity

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

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.

The Verdict

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.

{ 10 comments… read them below or add one }

G.M.kazim February 5, 2013 at 1:03 pm

dear i need u r help how use lookup

Reply

Swapan May 12, 2013 at 9:40 am

Actually I want how to use a Lookup Reference in my excel database. I have a student database, I want to make a student attendance sheet using this database. How can I do it?

Reply

cris June 3, 2013 at 4:46 pm

Need Excel function to perform lookup from multiple worksheets

Worksheet 1:
ITEM SALES AMOUNT
Jon Excel function to lookup from W/S 2

Worksheet 2:
ITEM SALES AMOUNT
Mary $500
Jon $200
Ben $800

Reply

sontoloyo June 15, 2013 at 7:47 pm

@chris:
Worksheet 1:
column “ITEM” as reference, assume Jon in A2
column “SALES” put this in B2 (without quote marks):
“=index(‘Worksheet 2′$B:$B,match(A2,’Worksheet 2′$A:$A,0))”
column “AMOUNT” put this in C2 (without quote marks):
“=index(‘Worksheet 2′$C:$C,match(A2,’Worksheet 2′$A:$A,0))”

Reply

manish September 2, 2013 at 5:48 am

please send me formulas in easy way to use
thanx

Reply

manish September 2, 2013 at 5:50 am

how linked the sheets with the help one sheet,s formulas use automaticaly applicable in next sheet
thanks

Reply

Brian October 9, 2013 at 2:11 pm

Sorry but your piece and matrix is missing one other important variables, how much the array datas need to be sorted and changed so the formula can work properly, and whether you need to use Control-Shift-Enter to enter the formula. I hate using control-Shift-Enter because it makes reusing and updating sheets when your data changes a pain. For example V and Hlookups when references other files only work when you have that file open. Sumproduct which you don’t have and should have in the list does not have the same issues. Sumproduct is maybe the most powerful function in excel.

Reply

ANAND KUMAR November 10, 2013 at 8:50 pm

hi

Reply

James November 11, 2013 at 8:43 am

I am not sure if this is the correct function for me, it’s close ….

I have a few codes associated with sites – 01 abc / 03 def / 07 ghi / 11 mno / 35 xyz

if i use lookup I think it rounds up / down. If I use IF ISNUMBER SEARCH and enter 1, it will return abc. If I enter 11 it will still return abc.

Is there a solution in Lookup that i clearly miss?

Reply

Zahir December 22, 2013 at 1:40 am

I have a table with first column showing start time (sub divided into two column between time a- time b), second column showing limit of working hours (sub divided into 6 column – for 1 leg duty, 2 legs duty to 6 legs duty). This table has 5 rows, I intend to get a formula so that by entering start time in a cell and no of leg in another, it will give me the limit from the table.

Reply

Leave a Comment

{ 5 trackbacks }

Previous post:

Next post: