Apr
18

Lookup in Excel: functions and formula examples

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 table

Excel Lookup functions

Below is a quick overview of the most popular formulas to perform lookup in Excel, their main advantages and drawbacks.

LOOKUP function

The LOOKUP function in Excel can perform the simplest types of vertical and horizontal lookups.

Pros: Easy-to-use.

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.

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

HLOOKUP function

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

VLOOKUP(lookup_value, lookup_table, MATCH(return_column_name, column_headers, 0), FALSE)

Formula for Horizontal lookup

HLOOKUP(lookup_value, lookup_table, MATCH(return_row_name, row_headers, 0), FALSE)

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:

OFFSET MATCH

A more complex but a more powerful lookup formula, free of many limitations of Vlookup and Hlookup.

Formula for V-Lookup

OFFSET(lookup_table, MATCH(lookup_value, OFFSET(lookup_table, 0, 1, ROWS(lookup_table), 1) ,0) -1, 0, 1, 1)

Formula for H-Lookup

OFFSET(lookup_table, 0, MATCH(lookup_value, OFFSET(lookup_table, ROWS(lookup_table) -1, 0, 1, COLUMNS(lookup_table)), 0) -1, 1, 1)

Formula for matrix lookup (by row and column)

OFFSET (starting_point, MATCH (vertical_lookup_value,  lookup_column, 0), MATCH (horizontal_lookup_valuelookup_row, 0))

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

INDEX MATCH

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

INDEX (return_column, MATCH (lookup_valuelookup_column, 0))

Formula for H-Lookup

INDEX (return_row, MATCH (lookup_value, lookup_row, 0))

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:

INDEX (lookup_table, MATCH (vertical_lookup_value, lookup_column, 0), MATCH (horizontal_lookup_value, lookup_row, 0))

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
Lookup        
Vlookup          
Hlookup          
Vlookup Match        
Hlookup Match        
Offset Match  
Offset Match Match        
Index 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:

VLOOKUP function

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

VLOOKUP MATCH

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)

Vlookup Match formula

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:

=INDEX($F$2:$F$6,(MATCH(H2,$B$2:$B$6,0)))

Index Match formula for left lookup

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:

HLOOKUP function

Can look up only across the top row in your data set.

=HLOOKUP(B8, $B$1:$F$5, 5, FALSE)

Hlookup formula

HLOOKUP MATCH

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.

Hlookup Match formula

INDEX MATCH

Can look up in any row, and has none of the limitations of the above formulas.

=INDEX($B$5:$F$5,(MATCH(B8,$B$1:$F$1,0)))

Index Match formula for upper lookup

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:

=OFFSET($A$1,MATCH(H2,$A$2:$A$6,0),MATCH(H3,$B$1:$E$1,0))

Two-dimensional lookup based on row and column values

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

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

=INDEX($A$1:$E$12,MIN(IF((ROW($A$1:$A$12)>MATCH(H2,$A$1:$A$12,0))*($A$1:$A$12=H3),ROW($A$1:$A$12),"")),MATCH(H4,$A$1:$E$1,0))

Three-dimensional lookup

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:

INDEX(lookup_table, MATCH(1,(lookup_value1=lookup_column1) * (lookup_value2=lookup_column2)*...,0), return_column_number)

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.

Lookup with multiple criteria

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:

IFERROR(INDEX( return_range, SMALL(IF( lookup_value=lookup_range, ROW( return_range )- m ,""), ROW() - n )),"")

Where:

  • 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.
Lookup to return multiple values

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:
Lookup from 2 tables

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:
Using a nested lookup formula in Excel

Case-sensitive lookup

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.

INDEX(return_column, MATCH(TRUE, EXACT(lookup_column, lookup_value),0))

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.
Case-sensitive lookup

For more formula examples, please see How to do case-sensitive lookup in Excel.

Look up by partial 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 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.
Lookup by partial match

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:

  1. Select your main table where you want to pull matching values.
  2. Select the lookup table to pull the matches from.
  3. Define one or more common columns.
  4. Choose the columns to be updated or/and added to the end of the table.
  5. Optionally, select one or more additional merge options.
  6. Click Finish and you will have a result in a moment!

Formula-free way to do lookup in Excel

If you are curious to try the add-in on your own worksheets, you are welcome to download a trial version. And if you decide to get a license, be sure to grab the 15% off coupon code that we provide exclusively to our blog readers: AB14-BlogSpo.

I thank you for reading and hope to see you on our blog next week!

8 Responses to "Lookup in Excel: functions and formula examples"

  1. Anand Kumar says:

    One of the Best Post.

    Thanks for sharing ☺..........

  2. Imran Masud says:

    Hi, Svetlana, Thanks for your post. I don't understand the Three dimensional lookup formula example (=INDEX($A$1:$E$12,MIN(IF((ROW($A$1:$A$12)>MATCH(H2,$A$1:$A$12,0))*($A$1:$A$12=H3),ROW($A$1:$A$12),"")),MATCH(H4,$A$1:$E$1,0))). Would you please explain it for my perusal?
    Thanks in advance. :)

    • Hi Imran,

      In truth, it is one of those formulas that are easier to write than explain :) Nevertheless, I will do my best to describe the general logic.

      At the core, you use the IF statement to find the number of the "Seller" (H3) row that comes after the "Year" (H2) row. Here's how:

      MATCH(H2,$A$1:$A$12,0) returns the row number of the year of interest, which is 7 (A7) in this example.

      (ROW($A$1:$A$12)>MATCH(H2,$A$1:$A$12,0)) compares each row in the table (A1:A12) with the above number and returns TRUE only for rows greater than 7, FALSE otherwise.

      ($A$1:$A$12=$H$3) compares the values in column A with the seller name in H3, and returns TRUE for the matches, FALSE otherwise.

      Then, you multiply the above 2 arrays of TRUE's and FALSE's. Remembering that TRUE equates to 1 and FALSE to 0, and multiplying by 0 always gives 0, you get 1 only for those rows that are after row 7 (the year 2017) and where the seller name matches the name in H3 (Sally in our example). For such rows, you get the IF function to return the row number (by putting ROW($A$1:$A$12) in the value_if_true argument), an empty string ("") otherwise.

      Since there may be a few occurrences of the same name in column A, you use the MIN function to get the first row number containing the desired name (that comes after the desired year).

      The rest of the formula is easy. You supply the above row number in the row_number argument of the INDEX function. To identify the column, you put MATCH(H4,$A$1:$E$1,0) in the column_number argument, where H4 is the month for which you want to return the amount. That's it!

  3. Imran Masud says:

    Lots of thanks, for your cooperation, Svetlana.

  4. Todd says:

    Hi,

    Firstly, apologies if this has been answered in another blog post previously!

    I have an Excel spreadsheet which is essentially a 'call schedule' with around 1,000 rows of client details,last contact dates, future contact dates (Column A) contact notes (Column N), etc.

    Column A11 - A1000 has the next 'due date' - there are unique and duplicate dates right throughout this column. Column D has the Client Name. Column N has the contact note (what I want to talk with people about) that I have set.

    What I would like to do is have the next 5 due dates show in order in cells D2 -D7, and then display the corresponding client name (E2 - E7) and contact note (F2 - F7).

    I have used the formula =small($A$11:$A:$1000,1) in D2 and then in E2 the formula =VLOOKUP(D2,$A$11:$N$1000,4,false) etc.

    The issue is that 'Vlookup' will only bring back the first response - if the date 02 June is the first 3 results for the 'small' function for example, it will show the same client name three times rather than the next... I believe it needs an 'index' and Array function, but that's beyond my excel knowledge!

    Anyone able to help?? Would greatly appreciate any assistance! Cheers.

  5. Amit H Sanghvi says:

    i have invoice worksheet n sales worksheet when i enter data in sales worksheet it get updated on invoice worksheet and i can print the invoice directly but i am not able to add more then 1 product lines in 1 invoice.. please help what formula do i need to use to more then one product in invoice

    • Hi, Amit,

      the point is that Excel lookup functions don't allow working with the tons of data at the same time. As a rule, the formula should be copied to every cell where you want to see your new value.

      However, we have a great tool that can update a whole worksheet with the data from your lookup table in a couple of clicks. Pleas take a look at our Merge Tables Wizard. You can download its fully functional trial version and try it out for yourself.

      Hope this helps!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard