Tutorial: How to Decide Which Excel Lookup Formula to Use

by Matthew Kuo on September 24, 2012

in Database Theory, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the

The Complete Guide to Excel Lookup Formulas

One of the most common tasks in 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 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.

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.

Click here for a tutorial on OFFSET MATCH MATCH

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.


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.

{ 24 comments… read them below or add one }

February 5, 2013 at 1:03 pm

dear i need u r help how use lookup


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?


cris June 3, 2013 at 4:46 pm

Need Excel function to perform lookup from multiple worksheets

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

Worksheet 2:
Mary $500
Jon $200
Ben $800


sontoloyo June 15, 2013 at 7:47 pm

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


manish September 2, 2013 at 5:48 am

please send me formulas in easy way to use


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


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.


ANAND KUMAR November 10, 2013 at 8:50 pm



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?


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.


khalid May 24, 2014 at 12:52 am

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.

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.


mike November 5, 2014 at 9:21 am

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.



crox January 13, 2015 at 11:44 am

im using lookup function to search for a text, for example
john 12
. .
. .
. .
kevin 124

Column A and B are text formated columns.

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


February 21, 2015 at 3:35 pm

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.


Excel_Anon May 12, 2015 at 6:05 am

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


Dee July 13, 2015 at 3:38 am

Hi, the MBA INDEX MATCH MATCH explanation is brilliant thank you, i am just having trouble as my formula is referring to cells that contain formula, and so is return a 0, what can i do about this ?


February 4, 2016 at 9:56 am

Which one of these is best for what I need: I have a database with multiple rows of the same value (name of study), and in a column I have number of studies performed for that particular study. On another sheet I want excel to sum up any time it sees the name of one particular study, say in January I have 4 studies performed and in February I have 3, so I want the second sheet to recognize and sum to equal 7 studies total. Completely new to the advanced functions of excel.


Matthew Kuo February 6, 2016 at 1:31 pm

Hi James,

Hard to tell without the actual spreadsheet, but it sounds like you need either the COUNTIF or the SUMIFS formula. This first allows you to count the number of appearances of a value in rows, whereas the latter allows you to sum (in your case the number of studies) in a particular cell.

Let me know if that helps.



Pat March 28, 2016 at 3:43 pm

Hi, can anyone suggest a best approach to returning the value of a cell based on multiple conditions in rows? Here is the data I have been given:
date Mar 13 Mar 13 Mar 13 Mar 28 28-Mar
time 12:00 13:12 14:15 16:00 7:25
Tasks 100 430 200 210 186
What I would like to return is how many tasks were produced at a specific date and time. For example, how many tasks were produced on Mar 13 at 12:20? It should be 100, but I ran into problems with Using index and match here as I didn’t have an exact time match. Any help would be appreciated!


laszlo kania October 26, 2016 at 4:45 am

Hi Pat, do you have an answer? If you send me an sample of your data to me e-mail I’ll try to work out the formula for you.


Aaron May 20, 2016 at 12:29 pm

So I have two databases that have a usecase ID, then account numbers and Dr/Cr for each use case, e.g.
Usecase, Acct, Dr/Cr
etc. I have multiple entries for each usecase ID, as each represents a SET of entries. I want to make sure that each set of entries in spreadsheet 1 equals each set in spreadsheet 2. The only method I can think of is to concatenate the three columns in each spreadsheet to a new column, and match that resultant column on sheet one to the resultant column on sheet 2. Is there a better way to lookup multiple columns and match them?


Rach February 13, 2017 at 10:17 am

Please help 🙂
I want to use a formula that looks up 1 part number from a table, finds that part number froma seperate column and then returns the value in the field after it:
Part Code Part Name of supplier
557 05903S Marcus and co
K3457 5925 Abbots
so first line is 5925 (Part), i want to find this data in the third column of the table (part) and then return the value from fourth column so for part number 5925 the answer would be Abbots (Name of Supplier).
What formula can i use please? Note the part number and suppliers name are quite extensive.


February 25, 2017 at 9:48 pm

anyone help


Mario June 24, 2017 at 5:53 am

Please help
I am trying to do just the inverse.
I would like to pick a value within a range and get the corresponding top and left header value.
I would like to pick Ruth and get “Middle” and “5” in the matrix look up
Is this possible?


Cancel reply

Leave a Comment

{ 9 trackbacks }

Previous post:

Next post:

Related pages

add chart title in excel 2010vlookup and functionexcel formula tipslookup across multiple sheetshow do you delete duplicates in exceloffset formula excelformula in excel sheetwhat is tax liabilities on w4excel macro to compare two columnsexcel 2013 data labelscreate vlookup in excelmatch formula in excel with exampleuse vlookup in vbausing vlookups3d column chart excelucla femba acceptance ratetracking sheet in excelexcel vba decimal placesmatch lookup excelnormal distribution data generatorif iserror excelucla anderson webmailvlookup exactiferror in exceldeduplication in excelshortcuts for excelsimple macro in excelexcel shortcutsforecast calculation in excelscheduling template for excelexcel error checkingoffset 1 0 vbaexcel chart data labelscountif greater than excelif functions excel 2013estimate worksheet templatesumproduct not workingmatch duplicates in excelfuzzy match excelcontest name pickerbootleg microsoft officewhat is the match function in excelcase study hbsthe goal a process of ongoing improvement sparknoteshistogram creator freeprimo converterharvard mba case studiesexcel 2010 compare two columns for duplicatesshortcut for filter in excelvlookup with choose functioncpk formula in excelexcel example formulashlook up excelexplain vlookup in excel with exampleexcel 2010 remove duplicatesname draw randomizerexcel if cell equals thenhow to do a vlookup for dummiesvlookup excel templatevlookup funtionselect distinct values in excelvslookup excelshortcut for paste in excelvlookup guidebar chart excel templateformula for count in excelucla universal studios ticketsmeaning of vlookupvlookup or hlookuphlookup formularandom partner generatorexcel nesting