ElyxAI

Excel Lookup and Reference Formulas

Functions for looking up values in tables, cell references, and creating dynamic links.

40 formulas
5 Beginner
29 Intermediate
6 Advanced

VLOOKUP

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

HLOOKUP

Intermediate
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

XLOOKUP

Intermediate
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

INDEX

Intermediate
=INDEX(array, row_num, [column_num])

MATCH

Intermediate
=MATCH(lookup_value, lookup_array, [match_type])

XMATCH

Intermediate
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

LOOKUP

Intermediate
=LOOKUP(lookup_value, lookup_vector, [result_vector])

OFFSET

Advanced
=OFFSET(reference, rows, cols, [height], [width])

INDIRECT

Advanced
=INDIRECT(ref_text, [a1])

ADDRESS

Intermediate
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

ROW

Beginner
=ROW([reference])

COLUMN

Beginner
=COLUMN([reference])

ROWS

Beginner
=ROWS(array)

COLUMNS

Beginner
=COLUMNS(array)

CHOOSE

Intermediate
=CHOOSE(index_num, value1, [value2], ...)

HYPERLINK

Beginner
=HYPERLINK(link_location, [friendly_name])

TRANSPOSE

Intermediate
=TRANSPOSE(array)

FILTER

Intermediate
=FILTER(array, include, [if_empty])

SORT

Intermediate
=SORT(array, [sort_index], [sort_order], [by_col])

SORTBY

Intermediate
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

UNIQUE

Intermediate
=UNIQUE(array, [by_col], [exactly_once])

CHOOSECOLS

Intermediate
=CHOOSECOLS(array, col_num1, [col_num2], ...)

CHOOSEROWS

Intermediate
=CHOOSEROWS(array, row_num1, [row_num2], ...)

TAKE

Intermediate
=TAKE(array, rows, [columns])

DROP

Intermediate
=DROP(array, rows, [columns])

EXPAND

Intermediate
=EXPAND(array, rows, [columns], [pad_with])

VSTACK

Intermediate
=VSTACK(array1, [array2], ...)

HSTACK

Intermediate
=HSTACK(array1, [array2], ...)

WRAPCOLS

Intermediate
=WRAPCOLS(vector, wrap_count, [pad_with])

WRAPROWS

Intermediate
=WRAPROWS(vector, wrap_count, [pad_with])

TOROW

Intermediate
=TOROW(array, [ignore], [scan_by_column])

TOCOL

Intermediate
=TOCOL(array, [ignore], [scan_by_column])

AREAS

Intermediate
=AREAS(reference)

FORMULATEXT

Intermediate
=FORMULATEXT(reference)

GETPIVOTDATA

Advanced
=GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...)

IMAGE

Intermediate
=IMAGE(source, [alt_text], [sizing], [height], [width])

RTD

Advanced
=RTD(ProgID, server, topic1, [topic2], ...)

GROUPBY

Advanced
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

PIVOTBY

Advanced
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], ...)

TRIMRANGE

Intermediate
=TRIMRANGE(reference, [rows_to_trim], [columns_to_trim])