*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:

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

**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.

{ 15 comments… read them below or add one }

dear i need u r help how use lookup

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?

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

@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))”

please send me formulas in easy way to use

thanx

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

thanks

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.

hi

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?

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.

Please let me know which is suitable excel function to resolve me issue

1- I have a cell say A1 filled with month name ” Jun”

2- A matrix with A-M (COL) with title (months name) and 7 (11:17) rows filled with numbers.

A B C D E F G H I J K L M

jan feb mar apr may jun jul aug sep oct nov dec

1 27 30 35 44 28 19 16 17 12 10 10 16

2 49 72 92 75 41 41 99 93 43 24 20 33

3 91 110 127 95 58 77 181 161 71 32 24 51

4 30 54 67 51 29 28 62 67 30 13 11 21

5 17 27 31 22 17 37 105 96 37 8 4 12

6 20 21 23 12 8 13 30 22 5 4 3 15

7 3 5 5 4 4 11 64 60 20 5 2 5

3- Now I want to fill a col Say A (row 21:27) in such that if A1 is jun then it select jun col from matrix and copy them in that col.

Hi, I want to update a spreadsheet with new contents but first I need to locate the serial numbers in the original worksheet, on the new worksheet. E.g.

W/S 1 – A1=Serial number, B1=item name, C1 = Item age, D1 = item colour

A2 = SH2431B

A3 = HJF24477

A4 = HGJKDK435

W/S 2 – A1=Serial number, B1=item name, C1 = Item age, D1 = item colour

A3 = SH2431B

.

.

.

A6 = HJF24477

.

.

A8 = HGJKDK435

So there are more contents in W/S2 than W/S1 but i want to find the serial numbers in WS1 from W/S and their corresponding values (B, C, D) either on a new worksheet or replacing any of the existing worksheets.

Please let me know if you dont understand what I have explained and I will send the sheet to you. Only that it is too large.

Thanks,

Mike

im using lookup function to search for a text, for example

A B

john 12

. .

. .

. .

kevin 124

Column A and B are text formated columns.

lookup(“124″,columnB,columnA) returns john. What did i miss?

It is absolutely incredible that Excel does not have a function that simply retrieves the value (Not index) of a data element in a table (A1:A100) or give an yes or no answer as to whether or not a search argument exists in the table.

Forty-five years ago when I first used IBM’s RPG it had (and still does) a simple op code LOKUP that does exactly that. My problem now is that I have a data set with records that include a 3-letter day of the week (Mon, Tue, etc.). COUNTIFS does fairly well as counting all records, but counting all Tue and Fri records or counting all except Sat and Sun records in a manner such that user can enter from one to seven day names on a work sheet and include or exclude only lrecords that have a matching day name is a real time-consuming task.

You need to use Subtotals which update based on filters, or Sumproduct.

{ 9 trackbacks }