Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to calculate current stock or inventory in Excel
  • Filter Data Based on Date in Excel
  • How to calculate average last N values in a table in Excel
  • Understanding Anova in Excel
  • How to combine 2 or more chart types in a single chart in Excel

References

  • How to use Excel VLOOKUP Function
  • Left Lookup in Excel
  • Count unique text values with criteria
  • How to use Excel MMULT Function
  • How to get address of named range in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation allow weekday only
  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only
  • Excel Data validation with conditional list

Complete List of Excel Lookup and Reference Functions, References and Examples

by

Excel comes with multiple Lookup and Reference functions that let you find matching values.

This page provides a complete Excel Lookup and Reference functions list. Each of the function links will take you to a dedicated page, where you will find a description of the function, with examples of use.

The Lookup and Reference Functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific values.

Here’s a full list of Built-In Lookup and Reference Functions in Excel and their Descriptions.

Click each of the function link to see detailed example.

Data Lookup Functions

HLOOKUP Looks up a supplied value in the first row of a table, and returns the corresponding value from another row
VLOOKUP Looks up a supplied value in the first column of a table, and returns the corresponding value from another column
LOOKUP Searches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector
GETPIVOTDATA Extracts data stored in a Pivot Table
CHOOSE Returns one of a list of values, depending on the value of a supplied index number
MATCH Finds the relative position of a value in a supplied array

Row / Column / Area Information

ROW Returns the row number of a supplied range, or of the current cell
COLUMN Returns the column number of a supplied range, or of the current cell
ROWS Returns the number of rows in a supplied range
COLUMNS Returns the number of columns in a supplied range
AREAS Returns the number of areas in a supplied range

Functions To Return References to Cell Ranges

ADDRESS Returns a reference, in text format, for a supplied row and column number
INDEX Returns a reference to a cell (or range of cells) for requested rows and columns within a supplied range
INDIRECT Returns a cell or range reference that is represented by a supplied text string
OFFSET Returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range

Others

HYPERLINK Creates a hyperlink to a document in a supplied location.
TRANSPOSE Performs a transpose transformation on a range of cells (i.e. transforms a horizontal range of cells into a vertical range and vice versa)
RTD Retrieves real-time data from a program that supports COM automation
FORMULATEXT Returns a formula as a string (New in Excel 2013)

 

Post navigation

Previous Post:

SUBSTITUTE function vs REPLACE function in Excel

Next Post:

Count cells between dates in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use IFS function in Excel
  • Nested IF function example in Excel
  • How to use Excel TRUE Function
  • How to use Excel FALSE Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Calculate total hours that fall between two times in Excel
  • Get month name from date in Excel
  • Convert Excel time to decimal minutes

Grouping

  • Group times into unequal buckets in Excel
  • Map inputs to arbitrary values in Excel
  • If cell contains one of many things in Excel
  • Group numbers at uneven intervals in Excel
  • Running count group by n size in Excel

General

  • How to get Excel workbook path only
  • Count cells that do not contain many strings in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Creating and Opening an existing file in Excel
  • How to calculate percentage of total in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning