The tutorial explains the basics of Lookup in Excel, shows the strengths and weaknesses of each Excel Lookup function and provides a number of examples to help you decide which lookup formula is best to be used in a particular situation.
Looking up a specific value within a dataset is one of the most common tasks in Excel. And yet, there exists no "universal" lookup formula suited for all situations. There reason is that the term "lookup" may denote a variety of different things: you can look vertically in a column, horizontally in a row or at the intersection of a row and column, search with one or several criteria, return the first found match or multiple matches, do a case-sensitive or case-insensitive lookup, and so on.
On this page, you will find a list of the most essential Excel Lookup functions with formula examples and in-depth tutorials linked for your reference.
Excel Lookup - the basics
Before we dive into the arcane twists of Excel Lookup formulas, let's define the key terms to ensure that we are always on the same page.
Lookup - searching for a specified value in a table of data.
Lookup value - a value to search for.
Return value (matching value or match) - a value at the same position as the lookup value but in another column or row (depending on whether you do vertical or horizontal lookup).
Lookup table. In computer science, a lookup table is an array of data, which is generally used to map input values to output values. In terms of this tutorial, an Excel lookup table is nothing else but a range of cells where you search for a lookup value.
Main table (master table) - a table into which you pull matching values.
Your lookup table and main table may have different structure and size, however they should always contain at least one common unique identifier, i.e. a column or row that holds identical data, depending on whether you want to perform a vertical or horizontal lookup.
The following screenshot shows a sample lookup table that will be used in many of the below examples.
Excel Lookup functions
Below is a quick overview of the most popular formulas to perform lookup in Excel, their main advantages and drawbacks.
The LOOKUP function in Excel can perform the simplest types of vertical and horizontal lookups.
Cons: Limited functionality, cannot work with unsorted data (requires sorting the lookup column/row in ascending order).
For more information, please see How to use Excel LOOKUP function.
It's an improved version of the LOOKUP function specially designed to do vertical lookup in columns.
Pros: Relatively easy to use, can work with exact and approximate match.
Cons: Cannot look at its left, stops working when a column is inserted into or removed from the lookup table, a lookup value cannot exceed 255 characters, requires much processing power on large datasets.
For more information, please see Excel VLOOKUP tutorial for beginners.
It's a horizontal counterpart of VLOOKUP that searches for a value in the first row of the lookup table and returns the value in the same position from another row.
Pros: Easy to use, can return exact and approximate matches.
Cons: Can only search in the topmost row of the lookup table, is affected by the insertion or deletion of rows, a lookup value should be under 255 characters.
For more information, please see How to use HLOOKUP in Excel.
VLOOKUP MATCH / HLOOKUP MATCH
A dynamic column or row reference created by MATCH makes this Excel lookup formula immune to the changes made in the dataset. In other words, with some help from MATCH, the VLOOKUP and HLOOKUP functions can return correct values no matter how many columns/rows have been inserted to or deleted from a lookup table.
Formula for Vertical lookup
Formula for Horizontal lookup
Pros: An improvement over regular Hlookup and Vlookup formulas immune to data insertion or deletion.
Cons: Not very flexible, requires a specific data structure (the lookup value supplied to the MATCH function should be exactly equal to the name of the return column), cannot work with lookup values exceeding 255 characters.
For more information and formula examples, please see:
A more complex but a more powerful lookup formula, free of many limitations of Vlookup and Hlookup.
Formula for V-Lookup
- n - is the lookup column offset, i. e. the number of columns to move from the starting point to the lookup column.
- m - is the return column offset, i. e. the number of columns to move from the starting point to the return column.
Formula for H-Lookup
- n - is the lookup row offset, i. e. the number of rows to move from the starting point to the lookup row.
- m - is the return row offset, i. e. the number of rows to move from the starting point to the return row.
Formula for matrix lookup (by row and column)
Please pay attention that this is an array formula, which is entered by pressing Ctrl + Shift + Enter keys at the same time.
Pros: Allows performing a left-side Vlookup, an upper Hlookup and two-way lookup (by column and row values), unaffected by changes in the data set.
Cons: Complex and difficult to remember syntax.
For more information and formula examples, please see: Using OFFSET function in Excel
It's the best way to do vertical or horizontal lookup in Excel that can replace most of the above formulas. The Index Match formula is my personal preference and I use it for almost all of my Excel lookups.
Formula for V-Lookup
Formula for H-Lookup
Formula for matrix lookup
An extension of the classic Index Match formula to return a value at the intersection of a specific column and row:
Cons: Just one - you need to remember the formula's syntax.
Pros: The most versatile Lookup formula in Excel, superior to Vlookup, Hlookup and Lookup functions in many respects:
- It can do left and upper lookups.
- Allows safely extending or collapsing the lookup table by inserting or deleting columns and rows.
- No limit to the lookup value's size.
- Works faster. Because an Index Match formula references columns/rows rather than an entire table, it requires less processing power and won't slow down your Excel.
For more information, please check out:
Excel Lookup comparison table
As you see, not all Excel Lookup formulas are equivalent, some can handle a number of different lookups while others can only be used in a specific situation. The table below outlines the capabilities of each Lookup formula in Excel.
|Formula||Vertical lookup||Left lookup||Horizontal lookup||Upper lookup||Matrix lookup||Allows data insertion/deletion|
|Offset Match Match||✓||✓|
|Index Match Match||✓||✓|
Excel lookup formula examples
The first step in deciding which formula to use in a specific situation is to determine what kind of lookup you want to perform. Below you will find formula examples for most popular lookup types:
Vertical lookup in columns
A vertical lookup or Vlookup is the process of finding a lookup value in one column and returning a value in the same row from another column. Vlookup in Excel can be done in a variety of ways, including:
If your lookup values reside in the left hand column of the table, and you do not plan to do any structural changes to your dataset (neither add nor delete columns), you can safely use a regular Vlookup formula:
=VLOOKUP(G2, $A$2:$E$6, 5, FALSE)
Where G2 is the lookup value, A2:E6 in the lookup table, and E is the return column.
If you are working with a "variable" Excel lookup table where columns can be inserted and deleted at any time, make your Vlookup formula immune to those changes by embedding the Match function that creates a dynamic column reference instead of a "hard-coded" index number:
=VLOOKUP(F2,$A$1:$D$6, MATCH($G$1,$A$1:$D$1, 0), FALSE)
INDEX MATCH - Left lookup
It's my favorite formula that handles right to left lookups with ease and works impeccably no matter how many columns you add or delete.
For example, to search column B for the value in H2 and return a match from column F, use this formula:
Note. When you plan to use a Vlookup formula in more than one cell, you should always lock the lookup table reference by using the $ sign (absolute cell reference), so that the formula gets copied correctly to other cells.
Horizontal lookup in rows
A horizontal lookup is a "transposed" version of vertical lookup that searches in a horizontally arranged dataset. In other words, it searches for the lookup value in one row, and returns a value in the same position from another row.
Assuming your lookup value is in B9, lookup table is B1:F5, and you want to return a matching value from row 5, use one of the following formulas:
Can look up only across the top row in your data set.
=HLOOKUP(B8, $B$1:$F$5, 5, FALSE)
Like pure Hlookup, this formula can search only in the topmost row, but allows you to safely insert or delete rows in the lookup table.
=HLOOKUP(B8, $B$1:$F$5, MATCH($A$9, $A$1:$A$5, 0), FALSE)
Where A1:A5 are row headers and A9 is the name of the row from which you want to return matches.
Can look up in any row, and has none of the limitations of the above formulas.
Two-dimensional lookup (based on row and column values)
Two-dimensional lookup (aka matrix lookup, double lookup or 2-way lookup) returns a value based on matches in both rows and columns. In other words, a 2-dimensional lookup formula searches for a value at the intersection of a specified row and column.
Assuming your lookup table is A1:E6, cell H2 contains the value to match on the rows and H3 holds the value to match on the columns, the following formulas will work a treat:
INDEX MATCH MATCH formula:
=INDEX($A$1:$E$6, MATCH(H2,$A$1:$A$6,0), MATCH(H3,$A$1:$E$1,0))
OFFSET MATCH MATCH formula:
Apart from the above formulas, there exist a handful of other ways to perform matrix lookup in Excel, and you can find full details in How to do 2-way lookup in Excel.
Three-dimensional lookup means searching by 3 different lookup values. In a data set below, supposing you want to search for a specific year (H2), then for a specific name within that year data (H3), and then return a value for a specific month (H4).
The task can be accomplished with the following array formula (please remember to press Ctrl + Shift + Enter to complete it correctly):
Lookup with multiple criteria
To be able to evaluate multiple criteria, we will need to modify the classic Index Match formula so that it turns into an array formula:
With the lookup table residing in A1:C11, let's find a match by 2 criteria: search column A for a value in cell F1, and column B for a value in cell F2:
=INDEX($A$1:$C$11, MATCH(1, (F1=$A$1:$A$11) * (F2=$B$1:$B$11),0), 3)
As usual, you press Ctrl + Shift + Enter for the formula to be evaluated as an array formula.
For the detailed explanation of the formula's logic, please see INDEX MATCH to look up with multiple criteria.
Lookup to return multiple values
Whichever Excel Lookup function you use (LOOKUP, VLOOKUP, or HLOOKUP), it can only return a single match. To get all found matches, you would have to employ 6 different functions combined in an array formula:
- m is the row number of the first cell in the return range minus 1.
- n is the row number of the first formula cell minus 1.
With the lookup value located in cell E2, lookup range in A2:A11, return range in B2:B11, and the first formula cell in row 2, your lookup formula takes the following shape:
=IFERROR(INDEX($B$2:$B$11, SMALL(IF($E$2 =$A$2:$A$11, ROW($B$2:$B$11 )- 1,""), ROW() - 1 )),"")
For the formula to return multiple matches, you enter it in the first cell (F2), press Ctrl + Shift + Enter, and then copy the formula to other cells down the column.
For the detailed explanation of the above formula and other ways to return multiple values, please see How to Vlookup to return multiple results.
Nested lookup (from 2 lookup tables)
In situations when your main table and the lookup table from which you want to pull data do not have a common column, you can use an additional lookup table to establish matches, like this:
To retrieve the values from the Amount column in Lookup_table2, you use the following formula:
=VLOOKUP(VLOOKUP(A2, Lookup_table1!$A$1:$B$6, 2, FALSE), Lookup_table2!$A$1:$B$6, 2, FALSE)
As shown in the screenshot below, our nested lookup formula works perfectly:
Sequential Vlookups from multiple sheets
To perform sequential Vlookups based on whether a previous lookup succeed or failed, use nested IFERROR functions together with VLOOKUPs to evaluate multiple conditions one by one:
If the first Vlookup fails, IFERROR traps the error and runs another Vlookup. If the second Vlookup does not find anything either, the second IFERROR catches the error and runs the third Vlookup, and so on. If all Vlookups fail, the last IFERROR returns "not found" or any other message you supply to the formula.
As an example, let's try to pull the amount from 3 different sheets:
=IFERROR(VLOOKUP(B1,A6:B9,2,0), IFERROR(VLOOKUP(B1,D6:E9,2,0), IFERROR(VLOOKUP(B1,G6:H9,2,0), "Not found")))
The result will look something similar to this:
For more information, please see How to use nested IFERROR functions in Excel.
As you probably know, all Excel Lookup functions are case-insensitive by their nature. To force your lookup formula to distinguish between lowercase and uppercase text, use either LOOKUP or INDEX MATCH in combination with the EXACT function. I personally opt for INDEX MATCH because it does not require sorting values in the lookup column as the LOOKUP function does, can perform both left-to-right and right-to-left lookups, and works perfectly for all data types.
With G2 being the lookup value, A - column to look up against and E - column to return the matches from, our case-sensitive lookup formula goes as follows:
=INDEX($E$2:$E$6, MATCH(TRUE, EXACT($A$2:$A$6,G2),0))
Since it's an array formula, be sure to press Ctrl + Shift + Enter to complete it properly.
For more formula examples, please see How to do case-sensitive lookup in Excel.
Lookup partial string match
Looking up by partial match is one of the most challenging tasks in Excel for which there exists no universal solution. Which formula to use depends on what kind of differences there are between your lookup values and values in the column to search in. In most cases, you'd use the LEFT, RIGHT or MID function to extract the common part of the values, and then supply that part to the lookup_value argument of the Vlookup function like it's done in the following formula:
=VLOOKUP(RIGHT(D2,4), $A$2:$B$6, 2, FALSE)
Where D2 is the lookup value, A2:B6 is the lookup table and 2 in the index number of the column to return the matches from.
For other ways to perform a partial match lookup in Excel, please see How to merge two worksheets by partial match.
This is how you use the Lookup functions in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our Excel Lookup formula examples.
Formula-free way to do lookup in Excel
It goes without saying that Excel lookup is not a trivial task. If you are taking your first steps in learning the realm of Excel, lookup formulas may seem quite confusing and difficult to understand. But please, don't feel discouraged, these skills do not come naturally to the majority of users!
To make things easier for novices, we created a special tool, Merge Tables Wizard, that can look up, match and merge tables without a single formula. In addition, it provides a number of really unique options that even advanced Excel users can benefit from:
- Lookup by multiple criteria, i.e. use one or several columns as the unique identifier(s).
- Update values in existing columns and add new columns from the lookup table.
- Return multiple matches in separate rows. When used in combination with the Combine Rows Wizard, it can even return multiple results in a single cell, comma or otherwise separated (an example can be found here).
- And more.
Working with the Merge Tables Wizard is easy and intuitive. All you have to do is:
- Select your main table where you want to pull matching values.
- Select the lookup table to pull the matches from.
- Define one or more common columns.
- Choose the columns to be updated or/and added to the end of the table.
- Optionally, select one or more additional merge options.
- Click Finish and you will have a result in a moment!
If you are curious to try the add-in on your own worksheets, you are welcome to download a trial version of our Ultimate Suite that includes all our time-saving tools for Excel (in total, 70+ tools and 300+ features!).