‘Lookup and reference’ Functions in Microsoft Excel 365!!
Tags: excelfunctions, lookupandreferencefu, excel, excel365tutorials
Published: 2020-08-11 06:05:00

---
title: ‘Lookup and reference’ Functions in Microsoft Excel 365!!
published: true
date: 2020-08-10 08:58:11 UTC
tags: ExcelFunctions,LookupandreferenceFu,Excel,Excel365Tutorials
canonical_url: https://geekexcel.com/lookup-and-reference-functions-in-microsoft-excel-365/
---

## Lookup and Reference Functions in Excel:

In this tutorial, we have listed the built-in **Lookup and reference Functions in Excel**.

These functions assist you to work with data arrays and it will be useful when you need to cross-reference between diverse data sets.

If you click on a function name or read more link, you will get a clear-cut description of that function with examples.

Note: Excel version is specified in some functions that indicate the introduction of that particular function in Excel. For example, 2013 indicates that the function will be available in Excel 2013 and all the newest versions.

| **S.No** | **Function** | **Explanation & Syntax** | **Read More** |
| 1 | [**ADDRESS Function**](https://geekexcel.com/how-to-use-address-function-in-microsoft-excel-365/) |  Creates a cell address from a given row and column.
```
=ADDRESS(row\_num, col\_num, [abs\_num], [a1], [sheet])
```
 | [**Read More about ADDRESS Function**](https://geekexcel.com/how-to-use-address-function-in-microsoft-excel-365/) |
| 2 | [**AREAS Function**](https://geekexcel.com/use-areas-function-in-microsoft-excel-365-in-easy-ways/) | Returns the number of areas in the mentioned reference.
```
=AREAS(reference)
```
 | [**Read More about AREAS Function**](https://geekexcel.com/use-areas-function-in-microsoft-excel-365-in-easy-ways/) |
| 3 | [**CHOOSE Function**](https://geekexcel.com/how-to-use-choose-function-in-microsoft-excel-2013/) | Returns a value from the list of value arguments using a given position or index.
```
=CHOOSE(index\_num, value1, [value2], ...)
```
 | [**Read More about CHOOSE Function**](https://geekexcel.com/how-to-use-choose-function-in-microsoft-excel-2013/) |
| 4 | [**COLUMN Function**](https://geekexcel.com/use-column-function-in-microsoft-excel-365-simple-methods/) | Returns the column number for a reference.
```
=COLUMN([reference])
```
 | [**Read More about COLUMN Function**](https://geekexcel.com/use-column-function-in-microsoft-excel-365-simple-methods/) |
| 5 | [**COLUMNS Function**](https://geekexcel.com/steps-to-use-columns-function-in-microsoft-excel-365-simple-way/) | Returns the count of columns in a given reference.
```
=COLUMNS(array)
```
 | [**Read More about COLUMNS Function**](https://geekexcel.com/steps-to-use-columns-function-in-microsoft-excel-365-simple-way/) |
| 6 | [**FORMULATEXT Function**](https://geekexcel.com/use-formulatext-function-in-microsoft-excel-365-simple-methods/) (2013) | Extracts a formula as text from a cell.
```
=FORMULATEXT(reference)
```
 | [**Read More about FORMULATEXT Function**](https://geekexcel.com/use-formulatext-function-in-microsoft-excel-365-simple-methods/) |
| 7 | [**GETPIVOTDATA Function**](https://geekexcel.com/easy-steps-to-use-getpivotdata-function-in-excel-365/) (2010) | Retrieves data from a pivot table, instead of cell references.
```
=GETPIVOTDATA(data\_field, pivot\_table, [field1, item1], ...)
```
 | [**Read More about GETPIVOTDATA Function**](https://geekexcel.com/easy-steps-to-use-getpivotdata-function-in-excel-365/) |
| 8 | [**HLOOKUP Function**](https://geekexcel.com/use-hlookup-function-in-microsoft-excel-365-in-easy-ways/) | Lookup and retrieve data from a specific row in the table.
```
=HLOOKUP(value, table, row\_index, [range\_lookup])
```
 | [**Read More about HLOOKUP Function**](https://geekexcel.com/use-hlookup-function-in-microsoft-excel-365-in-easy-ways/) |
| 9 | [**HYPERLINK Function**](https://geekexcel.com/how-to-use-hyperlink-function-in-microsoft-excel-365/) | Build links to workbook locations, pages on the internet, or to files on network servers.
```
=HYPERLINK(link\_location, [friendly\_name])
```
 | [**Read More about HYPERLINK Function**](https://geekexcel.com/how-to-use-hyperlink-function-in-microsoft-excel-365/) |
| 10 | [**INDEX Function**](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/) | Returns the value at a given position in a range or array.
```
=INDEX(array, row\_num, [col\_num], [area\_num])
```
 | [**Read More about INDEX Function**](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/) |
| 11 | [**INDIRECT Function**](https://geekexcel.com/use-indirect-function-in-microsoft-excel-365-in-easy-ways/) | Returns a valid reference from a given text string.
```
=INDIRECT(ref\_text, [a1])
```
 | [**Read More about INDIRECT Function**](https://geekexcel.com/use-indirect-function-in-microsoft-excel-365-in-easy-ways/) |
| 12 | [**LOOKUP Function**](https://geekexcel.com/use-lookup-function-in-microsoft-excel-365-simple-methods/) | 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(lookup\_value, lookup\_vector, [result\_vector])
```
 | [**Read More about LOOKUP Function**](https://geekexcel.com/use-lookup-function-in-microsoft-excel-365-simple-methods/) |
| 13 | [**MATCH Function**](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/) | Locates the position of a lookup value in a row, column, or table.
```
=MATCH(lookup\_value, lookup\_array, [match\_type])
```
 | [**Read More about MATCH Function**](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/) |
| 14 | [**OFFSET Function**](https://geekexcel.com/use-offset-function-in-microsoft-excel-365/) | Returns a reference to a range constructed in parts which are a starting point, a row, and column offset, and a final height and width in rows and columns.
```
=OFFSET(reference, rows, cols, [height], [width])
```
 | [**Read More about OFFSET Function**](https://geekexcel.com/use-offset-function-in-microsoft-excel-365/) |
| 15 | [**ROW Function**](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/) | Returns the row number for a reference.
```
=ROW([reference])
```
 | [**Read More about ROW Function**](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/) |
| 16 | [**ROWS Function**](https://geekexcel.com/use-rows-function-in-microsoft-excel-365-simple-methods/) | Returns the count of rows in a given reference.
```
=ROWS(array)
```
 | [**Read More about ROWS Function**](https://geekexcel.com/use-rows-function-in-microsoft-excel-365-simple-methods/) |
| 17 | [**TRANSPOSE Function**](https://geekexcel.com/how-to-use-transpose-function-in-microsoft-excel-365/) | Converts a vertical range to a horizontal range or a horizontal range to a vertical range.
```
=TRANSPOSE(array)
```
 | [**Read More about TRANSPOSE Function**](https://geekexcel.com/how-to-use-transpose-function-in-microsoft-excel-365/) |
| 18 | [**VLOOKUP Function**](https://geekexcel.com/use-vlookup-function-in-microsoft-excel-365-simple-steps/) | Lookup and retrieve data from a specific column in the table.
```
=VLOOKUP(value, table, col\_index, [range\_lookup])
```
 | [**Read More about VLOOKUP Function**](https://geekexcel.com/use-vlookup-function-in-microsoft-excel-365-simple-steps/) |

In the above article, you have learned the complete list of **Lookup and reference Functions in Excel**. This list assisted you to understand about each function by knowing its explanation, basic syntax, and examples.

Kindly share your **valuable comments** in the below box. Thanks for visiting **[Geek Excel](https://geekexcel.com/)** Keep Learning with us!!