Lookup & Reference
★ 80 · ◉ 371 · All versions
=MATCH()
Finds the position of a value within a range
=MATCH(lookup_value, lookup_array, [match_type])
Daily Tasks
Lookup & Retrieval
Lookup & Retrieval
15 functions for this use case · out of 12 defined
Lookup & Reference
★ 50 · ◉ 282 · Excel 365
=CHOOSECOLS()
Extracts the desired columns from a range and returns them in the order you want
=CHOOSECOLS(array, col_num1, [col_num2], ...)
Lookup & Retrieval
Reshape Tables
Lookup & Reference
★ 40 · ◉ 237 · Excel 365
=CHOOSEROWS()
Extracts the desired rows from a range and returns them in the order you want
=CHOOSEROWS(array, row_num1, [row_num2], ...)
Lookup & Retrieval
Sort, Filter & Dedupe
Lookup & Reference
★ 40 · ◉ 229 · All versions
=HLOOKUP()
Searches for a value in a row and returns the value found in the same row or rows below
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Lookup & Retrieval
Lookup & Reference
★ 40 · ◉ 218 · All versions
=LOOKUP()
Looks up a value in a sorted row or column and returns the corresponding value from another row or column
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Lookup & Retrieval
Lookup & Reference
★ 60 · ◉ 4 · All versions
=CHOOSE()
Returns one of several specified values based on a number
=CHOOSE(index_num, value1, [value2], ...)
Lookup & Retrieval
Conditional Logic
Database
★ 30 · ◉ 4 · All versions
=DGET()
Returns the only value in a database that matches the given criteria
=DGET(database, field, criteria)
Lookup & Retrieval
Lookup & Reference
★ 60 · ◉ 4 · All versions
=OFFSET()
Starts from a cell, moves by a specified number of rows and columns, and returns the new cell or range
=OFFSET(reference, rows, cols, [height], [width])
Lookup & Retrieval
Conditional Formatting & Validation
Lookup & Reference
★ 100 · ◉ 4 · Excel 2021
=XLOOKUP()
Searches a range and returns the corresponding value in the result range (row-wise or column-wise)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Daily Tasks
Lookup & Retrieval
Lookup & Reference
★ 90 · ◉ 2 · Excel 2021
=FILTER()
Filters data by one or more conditions and returns only the matching results
=FILTER(array, include, [if_empty])
Daily Tasks
Lookup & Retrieval
Lookup & Reference
★ 95 · ◉ 2 · All versions
=INDEX()
Returns the value at a specific position in a range
=INDEX(array, row_num, [column_num])
Daily Tasks
Lookup & Retrieval
Lookup & Reference
★ 55 · ◉ 2 · All versions
=INDIRECT()
Converts an address written as text into a real address
=INDIRECT(ref_text, [a1])
Lookup & Retrieval
Conditional Formatting & Validation
Lookup & Reference
★ 90 · ◉ 2 · All versions
=VLOOKUP()
Looks up a value in the first column of a range and returns information from another column
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Daily Tasks
Lookup & Retrieval
Lookup & Reference
★ 45 · ◉ 1 · All versions
=GETPIVOTDATA()
Returns a specific number from inside a PivotTable based on field and item names
=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
Lookup & Retrieval
Reporting
Lookup & Reference
★ 70 · ◉ 1 · Excel 2021
=XMATCH()
Finds the position (relative row or column number) of a value in a range or array; the modern, more complete version of MATCH
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Lookup & Retrieval
Comparation