Lookup Functions in Excel

Excel can select a specific value from a long list of values. These are called lookup functions. The most important lookup functions are VLOOKUP and XLOOKUP.

Retrieving Data with Lookup Functions

Data is often stored in a list on a worksheet. A list that categorizes values is called a lookup table.The lookup and reference functions allow you to find a value based on the lookup table criteria.

These functions allow the use of different input values based on criteria to produce an output.

Microsoft has an article listing all the lookup and reference functions in Excel.

Retrieving from Columns and Rows

VLOOKUP and HLOOKUP are common lookup functions. VLOOKUP is a vertical lookup that works when the lookup table is organized in columns. 

HLOOKUP is a horizontal lookup, so the lookup table needs to be organized in rows.

VLOOKUP

VLOOKUP retrieves data from a specific column in a table. VLOOKUP supports approximate and exact matching and wildcards (* and ?) for partial matches. The “V” stands for “vertical”. Lookup values must appear in the first column of the table, with lookup columns to the right.

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value to look for in the first column of a table.
• table_array: The table from which to retrieve a value.
• col_index_num: The column in the table from which to retrieve a value.
• range_lookup: [optional] TRUE = approximate match (default). FALSE = exact match

Argument	Description
lookup_value	Data to look up; can be number, text, logical value, name, or cell reference
table_array	The range containing the data you want to search; the lookup_value must be in the first column
col_index_num	The number of the column containing the data you want to retrieve; 1, 2, 3, etc.
range_lookup	TRUE: greatest value not exceeding the lookup_value; FALSE: exact match

See VLOOKUP Tutorial for Excel

HLOOKUP

HLOOKUP retrieves data from a specific row in a table. The “H” in HLOOKUP stands for “horizontal”, where lookup values appear in the first row of the table, with the data below the first row. HLOOKUP supports approximate and exact matching and wildcards (* and ?) for finding partial matches.

=HLOOKUP (value, table, row_index, [range_lookup])

• value: The value to look up.
• table: The table from which to retrieve data
• row_index: The row number from which to retrieve data
• range_lookup: [optional] TRUE = approximate match (default). FALSE = exact match

Argument	Description
lookup_value	Data to look up; can be number, text, logical value, name, or cell reference
table_array	range containing the data you want to search; the lookup_value must be in the first row
row_index	The number of the row containing the data you want to retrieve; 1, 2, 3, etc.
range_lookup	TRUE: greatest value not exceeding the lookup_value; FALSE: exact match

XLOOKUP

XLOOKUP is a new function that was released in early 2020. It is a new function available for Office 365 subscribers.

XLOOKUP is intended to replace both VLOOKUP and HLOOKUP. If you need your spreadsheets to be compatible with others with older versions of Excel, you still need to use VLOOKUP or HLOOKUP. For new spreadsheets for your personal use, you should start using XLOOKUP.

For more on this new function, see XLOOKUP Tutorial for Beginners

XLOOKUP

Searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used.

=XLOOKUP(lookup_value,lookup_array,return_array,match_mode,search_mode)

• Lookup_value: is the value to search for.
• Lookup_array: is the array or range to search.
• Return_array: is the array or range to return.
• Match_mode: specify how to match lookup_value against the values in lookup_array.
• Search_mode: specify the search mode to use. By default, a first-to-last search will be used.

Argument	Description
lookup_value	value to search for; can be number, text, logical value, name, or cell reference
lookup_array	The range containing the data you want to search
return_array	The range containing the data to retrieve
match_mode	0 exact match, -1 exact match or next smaller item, 1 exact match or next larger item, 2 wildcard character match
search_mode	1 search first to last, -1 search last to first

LOOKUP

The LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range.

LOOKUP’s default behavior makes it useful for solving certain problems in Excel.

=LOOKUP (lookup_value, lookup_vector, [result_vector])

• lookup_value: The value to search for.
• lookup_vector: The one-row, or one-column range to search.
• result_vector: [optional] The one-row, or one-column range of results.

Argument	Description
lookup_value	value that LOOKUP searches for in Lookup_vector and can be a number, text, a logical value, or a name or reference to a value.
lookup_vector	range that contains only one row or one column of text, numbers, or logical values, placed in ascending order.
result_vector	range that contains only one row or column, the same size as Lookup_vector.

More lookups

VLOOKUP can retrieve data from columns, and HLOOKUP can retrieve data from rows. The INDEX function can retrieve data from multidimensional tables (both columns and rows). The INDEX function can retrieve a value when given a row and column.

The INDEX function is often used with the MATCH function, which can feed both the column and row to the INDEX function.

Argument	Description
INDEX	Returns a value or reference of the cell at the intersection of a particular row and column in a given range.
MATCH	Returns the relative position of an item in an array that matches a specified value in a specified order.
CHOOSE	Chooses a value or action to perform from a list of values, based on an index number.
INDIRECT	Returns the reference specified by a text string
OFFSET	Returns a reference to a range that is a given number of rows and columns from a given reference

INDEX and MATCH

INDEX is often used with the MATCH function(s) to achieve a similar result to using the VLOOKUP. The formula would be INDEX(array,MATCH,MATCH).

INDEX

Returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

=INDEX (array, row_num, [col_num], [area_num])

Array Form
=INDEX (array, row_num, [col_num]) 

Reference Form
=INDEX (reference, row_num, [col_num], [area_num])

• array: a range of cells, or an array constant
• row_num: The row position in the reference or array.
• col_num: [optional] The column position in the reference or array
• area_num: [optional] The range in reference should be used.

MATCH

Locates the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching and wildcards (* and ?) for partial matches. Often, users combine the INDEX function with the MATCH function.

=MATCH (lookup_value, lookup_array, [match_type])

• lookup_value: The value to match in lookup_array.
• lookup_array: – A range of cells or an array reference.
• match_type: [optional] How to match, specified as –1, 0, or 1. The default is 1.

CHOOSE

The CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(2, “red”, “blue”, “green”) returns “blue”, since blue is the second value listed after the index number. The values provided to CHOOSE can include references.

=CHOOSE (index_num, value1, [value2], …)

• index_num: The value to choose. A number between 1 and 254.
• value1: The first value from which to choose.
• value2: [optional] The second value from which to choose

IS Functions

Argument	Description
ISBLANK	value is an empty cell
ISERR	any error value except #N/A
ISERROR	any error value
ISLOGICAL	logical value
ISNA	#N/A error value
ISNONTEXT	any item that is not text
ISNUMBER	value is a number
ISREF	value is a reference
ISTEXT	value is a text

ISBLANK

The ISBLANK function returns TRUE when a cell is empty and FALSE when a cell is not empty. For example, if A1 contains “apple”, ISBLANK(A1) returns FALSE. 

=ISBLANK (value)

value: The value to check.

IFERROR

IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

Find and handle errors: errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

=IFERROR (value, value_if_error)

• value: The value, reference, or formula to check for an error.
• value_if_error: The value to return if an error is found.

Dynamic Arrays

There are Excel formulas that can return arrays of variable sizes. These new functions are called dynamic arrays. The dynamic array functions can spill into additional cells.

Function	Description
FILTER	filter a range or array
RANDARRAY	Returns an array of random numbers
SEQUENCE	Returns a sequence of numbers
SORT	Sorts a range or array
SORTBY	Sorts a range or array based on the values in a corresponding range or array
UNIQUE	Returns the unique values from a range or array
@	Prevent from spilling

Video: Excel VLOOKUP Function Examples

https://youtu.be/VVD1GXPMwIg

---

Video: Excel XLOOKUP Function Examples

https://youtu.be/4NyXbNVQ7s8

---

---