Lookup Functions in Excel


Retrieving Data with Lookup Functions

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

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

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.

Retrieving from Columns and Rows

VLOOKUP and HLOOKUP are the most common lookup functions. VLOOKUP is a vertical lookup which works when the lookup table is organized in columns. HLOOKUP is a horizontal lookup, so the lookup table needs to be organized in rows.

ArgumentDescription
lookup_valueData to look up; can be number, text, logical value, name, or cell reference
table_arrayThe range containing the data you want to search; the lookup_value must be in the first column
col_index_numThe number of the column containing the data you want to retrieve; 1, 2, 3, etc.
range_lookupTRUE: greatest value not exceeding the lookup_value; FALSE: exact match

VLOOKUP

VLOOKUP retrieves data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) 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

ArgumentDescription
lookup_valueData to look up; can be number, text, logical value, name, or cell reference
table_arrayThe range containing the data you want to search; the lookup_value must be in the first row
col_index_numThe number of the row containing the data you want to retrieve; 1, 2, 3, etc.
range_lookupTRUE: greatest value not exceeding the lookup_value; FALSE: exact match

HLOOKUP

HLOOKUP retrieves data from a specific row in 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 (* ?) 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

ArgumentDescription
lookup_valuevalue 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_vectorrange that contains only one row or one column of text, numbers, or logical values, placed in ascending order.
result_vectorrange that contains only one row or column, the same size as Lookup_vector.

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: Meet the New Excel Boss

ArgumentDescription
lookup_valuevalue to search for; can be number, text, logical value, name, or cell reference
lookup_arrayThe range containing the data you want to search
return_arrayThe range containing the data to retrieve
match_mode0 exact match, -1 exact match or next smaller item, 1 exact match or next larger item, 2 wildcard character match
search_mode1 search first to last, -1 search last to first

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.

LOOKUP

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.

Retrieving from Multidimensional Tables

VLOOKUP can retrieve data from columns and HLOOKUP retrieves 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 that can feed both the column and row to the INDEX function.

ArgumentDescription
INDEXReturns a value or reference of the cell at the intersection of a particular row and column, in a given range.
MATCHReturns the relative position of an item in an array that matches a specified value in a specified order.
CHOOSEChooses a value or action to perform from a list of values, based on an index number.
INDIRECTReturns the reference specified by a text string
OFFSETReturns a reference to a range that is a given number of rows and columns from a given reference.

INDEX/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 that 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 (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

=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. Default is 1.

CHOOSE

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 2nd 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

ArgumentDescription
ISBLANKvalue is an empty cell
ISERRany error value except #N/A
ISERRORany error value
ISLOGICALlogical value
ISNA#N/A error value
ISNONTEXTany item that is not text
ISNUMBERvalue is a number
ISREFvalue is a reference
ISTEXTvalue is a text

ISBLANK

ISBLANK function returns TRUE when a cell contains 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.

FunctionDescription
FILTERfilter a range or array
RANDARRAYReturns an array of random numbers
SEQUENCEReturns a sequence of numbers
SORTSorts a range or array
SORTBYSorts a range or array based on the values in a corresponding range or array
UNIQUEReturns the unique values from a range or array
@Prevent from spilling

Video: Excel VLOOKUP Function Examples


Video: Excel XLOOKUP Function Examples


Jeff Mankin

Jeff Mankin teaches financial literacy. His website is FinallyLearn.com.

Recent Posts

link to Expense

Expense

What is an expense? Expense is a cost to operate a business to produce revenue. In accounting, an expense occurs when an asset is used. This could include a cash outflow or consuming an...