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.
Click here for a tutorial on VLOOKUP HLOOKUP
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:
- 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.
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.
From some time in your future:
With your matrix in columns A:M, your lookup value cannot be in A1. However, if it was in A20 then in A21 put =OFFSET(INDEX($A$1:$L$8,2,MATCH($A$20,$A$1:$L$1,0)),ROW()-21,0) and copy it down.
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.
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 ?
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.
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.
Best,
Matthew
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!
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.
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
_____12,_423,__Dr
_____12,_127,__Cr
_____12,__47,__Dr
_____13,_172,__Dr
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?
Thanks
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
5925 5509135857 05903S Marcus and co
K34102 5509135857 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.
anyone help
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.
Eg
I would like to pick Ruth and get “Middle” and “5” in the matrix look up
Is this possible?
“Insert Column Immunity”
Are there special settings to ensure this works?
Doesn’t seem to follow the relative position rule for lookup where or dynamic where the Match formula makes your column reference dynamic.
BTW — AWESOME WORK ON THIS SITE/ VERY VERY HELPFUL. Thank you.