LOOKUPS in Excel Explained
Lookup functions are some of the most used Excel functions.
What is a Lookup?
I’m glad you asked. Lookups effectively search for a particular value in a row or column and return a corresponding value from another row or column. A Lookup allows you to search for specific values based on neighboring values.
They can be incredibly useful when you have large datasets or many worksheets that you need to search across.
In this guide to Lookups, we explain the different Lookup functions. We start with LOOKUP, then move onto Vertical Lookup (VLOOKUP), Horizontal Lookup (HLOOKUP), and end with the incredibly powerful XLOOKUP and INDEX-MATCH functions.
First, we’ll look at the syntax for the basic LOOKUP function and how those parameters break down.
Table of Contents
1. LOOKUP
The LOOKUP function searches for a value in the lookup_vector and returns the corresponding value in the result_vector.
Syntax
=LOOKUP (lookup_value, lookup_vector, [result_vector])
If that sounds confusing, don’t worry, we’ll explain everything with a few examples in a moment.
The best way to look at any function is to work through its syntax. By breaking down the function like this, we start to understand how it operates.
Parameters
- lookup_value – Specifies the value to search for in the lookup range (lookup_vector).
- lookup_vector – Specifies a single row or column of data in ascending order that you want the function to search in.
- result_vector – This is optional. The information in the result_vector is returned if the result_vector parameter is used!
Example 1
Here we are looking up the salary of employee Number 23. Our Lookup value is 23, our lookup vector is A2 to A5, and our results vector is D2 to D5.
Example 2
The LOOKUP function comes into its own when you understand how it matches values when it cannot find an exact match.
In this example, the LOOKUP function cannot find an exact match:
=LOOKUP(1010, A3:A6, B3:B6)
Therefore, it chooses the next highest value in the lookup_vector that is less than or equal to the lookup_value.
In this instance, as the value 1010 is not available in the lookup_range, Excel chooses the highest value below 1010, which in this case is 1008. As we have a result_range in this example, the corresponding value is ORANGE. Therefore, ORANGE is our result in G1.
2. VLOOKUP
VLOOKUP stands for ‘Vertical Lookup’. It searches for a specified value in a column and returns the result from another column in the same row. VLOOKUP is probably the most famous of the Lookup family and is likely to be a Lookup you use often.
Syntax
=VLOOKUP (lookup_value,table_array,col_index_num,[range_lookup)
Parameters
- lookup_value – This is the same as in the LOOKUP function. It’s the value we are searching for in our lookup_range.
- table_array – This is the column range in which we are looking for our lookup_value. This can be an entire column, a fixed range, a table range or a named range.
- col_index_num – This numerical value represents how many columns you want the formula to look from your table_array. For example, if your lookup range is column A, and you put ‘3’ as your column index number, you will return the corresponding value in column ‘C’.
- range _lookup – It accepts two values, one is TRUE, and another is FALSE.
- If you are happy with the closest match, rather than an exact match, then use TRUE.
- If you want an exact match, then use FALSE.
Note : This parameter is optional and returns TRUE by default if you do not select one in the formula.
Example
In this example, our lookup_value is C7, our lookup range (table_array) is B2 to B5, and we’re looking three columns across to the salary column and asking for an exact match (FALSE). The VLOOKUP formula returns Andrew’s salary as a result.
3. HLOOKUP
The HLOOKUP function in excel searches for a value in a table horizontally. HLOOKUP looks for a value in the top row of a specified table, or array, and then returns the corresponding value in a lower row, based on how many rows you specify. It’s identical to a VLOOKUP but works horizontally instead of vertically!
Syntax
=HLOOKUP (lookup_value,table_array,row_index_num,[range_lookup])
Parameters
- lookup_value – Again, this is the value we are searching for in our table_array.
- table_array – This is the row range in which we are looking for our lookup_value. This can be an entire row, a fixed range, a table range or a named range.
- row_index_num – This numerical value represents how many rows you want the formula to look from your table_array.
- range_lookup – This is an optional parameter that returns an exact match when FALSE is used and an approximate match or closest match when TRUE is used.
Example
In this example, we are looking for the author of a particular subject. We look up C2 and ask HLOOKUP to return the author from row three.
4. XLOOKUP
The XLOOKUP function was introduced as an improvement over the older functions like LOOKUP, VLOOKUP, HLOOKUP. You can only access the XLOOKUP function if you have Microsoft 365 (previously Office 365), or Microsoft Excel 2021 or later.
XLOOKUP is awesome, and a big improvement on the VLOOKUP, LOOKUP and HLOOKUP options because it can look up data anywhere in a table_array or lookup_range. VLOOKUP is restricting as it can only look for data from left to right, and HLOOKUP can only look for data from top to bottom. XLOOKUP makes looking up in any direction possible!
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Parameters
- lookup_value – This is the value we are looking for in our lookup_array.
- lookup_array – This is the table range where we search for our lookup_value.
- return_array – This is the array that we want a return from. We’re asking XLOOKUP to match the value in the lookup_array and return the corresponding value in the return_array.
- if_not_found – This is an optional parameter. It dictates what we want XLOOKUP to return if there is no match. If it is not specified and no match is found, it generates a N/A error.
match_mode and search_mode extend the capabilities of XLOOKUP and allow you to add options to return the next smaller items or perform a return search. The uses here are broad, so we’ll cover how to use them in an article dedicated to XLOOKUP!
Example
In this example, we’re looking up the first name of someone in column B, based on their last name in column C. XLOOKUP allows us to look vertically from right to left in this instance. Something not possible with a VLOOKUP!
5. INDEX and MATCH
INDEX and MATCH combined are two of the most valuable functions in Excel for performing more advanced lookups. For example, you can use INDEX MATCH to perform horizontal and vertical lookups. If you want to be a master in Excel, learning INDEX and MATCH is a must.
INDEX MATCH is also available on most versions of Excel, unlike XLOOKUP. INDEX and MATCH are two separate functions that you can combine to achieve a look-up effect. Let’s take a look at them separately before combining them.
INDEX
The INDEX function returns a value based on the table_range specified.
Syntax
=INDEX(array,row_number)
Parameters
- array – This is the lookup range where the value is present. It’s where we want the formula to look.
- row_number – This is the row number from the starting index.
Example
In this example, we are looking at range B1 to B4 and asking Excel to return the value from the third row. In this case, cell B3.
The INDEX function can also return a value from two or more columns. In this instance, you have to include the row index and the column index.
| =INDEX(array,row_number,[col_number]) |
MATCH
The MATCH function finds the position of a value in a given range and returns that value. It searches for a value both horizontally and vertically.
Syntax
=MATCH(lookup_value,lookup_array)
Parameters
- lookup_value – Explicitly mention the value here. If it is a text, enclose it in double-quotes.
- lookup_array – This is the lookup range.
Example 1
In this example, we ask Excel to look for the word “Grapes” in the range B2 to B5. “Grapes” is in the fourth row, so MATCH returns 4.
Example 2
If the exact value you are looking for is inside the formula, enclose it within double quotes. Otherwise, you can refer to a cell reference.
In this example, we refer to cell B8 as our lookup_value to complete our MATCH.
INDEX and MATCH Combined
You can use the MATCH function in combination with the INDEX function. When used together, you nest (embed) the MATCH function within an INDEX formula so that the MATCH element looks up which row the INDEX function should index.
Syntax
=INDEX(array,MATCH(lookup_value,lookup_array,0))
If that doesn’t make sense, here’s an example to help!
Example
In this example, we index the range D2 to D5 and then deploy our MATCH function to look up where within the range A2:A5 the value in C7 is. In this case, it’s looking up the value ’23’. This combination of INDEX and MATCH returns the adjacent value in column D for us – 15,000.
Frequently Asked Questions
1. How do you differentiate between VLOOKUP and LOOKUP?
The simplest difference between VLOOKUP and LOOKUP is the cross-functionality that LOOKUP has. VLOOKUP is limited to vertical lookups.
2. How do you define a LOOKUP Vector?
A LOOKUP Vector is used to look up a value in a one row/range area (Vector) and returns it from the exact position in a second one-row or one-column range.
3. How is XLOOKUP better than the INDEX_MATCH function in Excel?
The problem with the INDEX-MATCH function is that the formula is long and complex, whereas the XLOOKUP function has a simple syntax.
Wrapping Up
This guide explains what Lookup Functions do and walks you through the most common Lookups available.
We started with the basic LOOKUP function and then walked you through VLOOKUP and HLOOKUP.
LOOKUP, VLOOKUP and HLOOKUP all have limitations. So, we looked at some alternatives in the form of XLOOKUP and INDEX MATCH.
In summary, if you need to use a Lookup and have the option to use XLOOKUP, we recommend sticking with that. It’s more flexible than the other Lookup functions and less complicated than INDEX MATCH. XLOOKUP also allows you to add many additional conditions, making it far more valuable as your lookups become more complex.
If you enjoyed this article and are looking to take your Excel skills to the next level Academy of Learning offers world class courses in Excel and other Microsoft applications. Not sure if you’re ready to take the next step? Check out our article on 3 reasons to learn Microsoft Office, Professionally.
