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

Contents

## 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 |

## 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

## Video: Excel XLOOKUP Function Examples

## Excel Basics Lessons for Beginners

Here are the Excel basics lessons for beginners in The Ultimate Guide to Finally Learn Excel.

- Introduction to Excel
- Statistical Analysis in Excel
- Creating Graphs in Excel
- Logical Functions in Excel
- Text Functions in Excel
- Financial Functions in Excel
- Lookup Functions in Excel
- Data Analysis in Excel
- What-if Analysis in Excel

See Also:

Jeff Mankin teaches financial literacy and Excel. He is the founder of Finally Learn.