Excel Lookup Functions

puneet-gogia-excel-champs

- Written by Puneet

sample files

1. ADDRESS Function

The ADDRESS Function returns a valid cell reference as per the column and row address. In simple words, you can create an address of a cell by using its row number and column number.

Syntax

ADDRESS(row_num,column_num,abs_num,A1,sheet_text)

Arguments

  • row_num: A number to specify row number.
  • column_num: A number to specify column number.
  • [abs_num]: Reference type.
  • [A1]: Reference style.
  • [sheet_text]: A text value as a sheet name.

Notes

  • By default, the ADDRESS function returns absolute reference in the result.

Example

In the below example, we have used different arguments to get all types of results.

excel-address-function-example-1

With R1C1 reference style:

  • Relative reference.
  • Relative row and absolute column reference.
  • Absolute row and relative column reference.
  • Absolute reference.

With A1 reference style:

  • Relative reference.
  • Relative row and absolute column reference.
  • Absolute row and relative column reference.
  • Absolute reference.

2. AREAS Function

The AREAS Function returns a number that represents the number of ranges in the reference you have specified. In simple words, it actually counts the different worksheet areas you have referred to the function.

syntax

AREAS(reference)

Arguments

  • reference: A Reference to a cell or a range of cells.

Notes

  • Reference can be a cell, a range of cells or a named range.
  • If you want to refer to more than one cell reference, you have to enclose all those references in more than one set of parentheses and use commas to separate each reference from others.

Example

In the below example, we have used areas function to get the number reference in a named range.

Excel-Areas-Example-1

As you can see there are three columns in the range and it has returned 3 in the result.

excel-Area-function-example-2 (1)

3. CHOOSE Function

The CHOOSE function returns a value from the list of values based on the position number specified. In simple words, it looks for a value from a list based on its position and returns it in the result.

Syntax

CHOOSE(index_num,value1,value2,…)

Arguments

  • index_num: A number for specifying the position of the value in the list.
  • value1: A range of cells or an input value from which you can choose.
  • [value2]: A range of cells or an input value from which you can choose.

Notes

  • You can refer to a cell or you can also insert values directly in the function.

Example

In the below example, we have used CHOOSE function with a drop-down list to calculate four(sum, average, max, and mix) different things. So, we have used the below formula to calculate all four things:

=CHOOSE(VLOOKUP(K2,Q1:R4,2,FALSE),SUM(O2:O9),AVERAGE(O2:O9),MAX(O2:O9),MIN(O2:O9))

excel-choose-function-example-1

We have this small table with the name of all four calculations which we want and a serial number to each in the corresponding cell.

After that, we have a drop-down list for all four calculations. Now, to get index number in the choose function from that small table we have a lookup formula which will returns serial number as per the value selected from the drop-down list.

And instead of values, we have used four formulas for 4 different calculations.

4. COLUMN Function

The COLUMN function returns the column number for the given cell reference. As you know, every cell reference is made up of a column number and a row number. So it takes the column number and returns it in the result.

Syntax

COLUMN([reference])

Arguments

  • reference: A cell reference for which you want to get the column number.

Notes

  • You cannot refer to multiple references.
  • If you refer to an array, the column function will also return the column numbers in an array.
  • If you refer to a range of more than one cell, it will return the column number of the leftmost cell. For example, if you refer to the range A1:C10, it will return the column number of the cell A1.
  • If you skip specifying a reference, it will return the column number of the current cell.

Example

In the below example, we have used COLUMN to get the column number of the cell A1.

excel-column-function-example-1

As I have already mentioned, if you skip specifying cell reference it will return the column number of the current cell. In the below example, we have used COLUMN to create a header with serial numbers.

excel-column-function-example-2

5. COLUMNS Function

The COLUMNS function returns the number of columns referred to in the given reference. In simple words, it counts how many columns are there in the supplied range and returns that count.

Syntax

COLUMNS(array)

Arguments

  • array: An array or range of cells from which you want to get the number of columns.

Notes

  • You can also use a named range.
  • COLUMNS function is not concerned with the values in the cells, it will simply return the number of columns in a reference.

Example

In the below example, we have used COLUMN to get the number of columns from range A1:F1.

excel-columns-function-example-1

6. FORMULATEXT Function

The FORMULATEXT function returns the formula from the referred cell. And if there’s no formula in the referred cell, a value, or a blank, it will return a #N/A.

Syntax

FORMULATEXT(reference)

Arguments

  • reference: The cell reference from which you want formula as a text.

Notes

  • If you refer to another workbook that workbook should be open, otherwise it will not show the formula.
  • If you refer to a range more than a single cell, it will return formula from the upper-left cell of the given range.
  • It will return an “#N/A” error value if the cell you are using as a reference does not contain any formula, has a formula with more than 8192 characters, a cell is protected, or an external workbook is not opened.
  • If you refer two cells in circular reference it will return results from both.

Example

In the below example, we have used formula text with different types of references. When you refer to a cell that doesn’t have any formula, it will return “#N/A” error value.

excel-formulatext-function-example-1

7. HLOOKUP Function

The HLOOKUP function lookups for a value in the top row of a table and returns the value from the same column of the matched value using the index number. In simple words, it performs a horizontal lookup.

Syntax

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

Arguments

  • lookup_value: The value you want to lookup.
  • table_array: The data table or an array from which you want to the lookup value.
  • row_index_num: A numeric value representing a number of rows below from the top row from which you want the value. For example, if you specify 2 and your lookup value is in A10 in the data table, it will return value from cell B10.
  • [range_lookup]: A logical value to specify the type of lookup. If you want to perform an exact match search use FALSE and if you want to perform a non-exact match use TRUE (Default).

Notes

  • You can use wildcard characters.
  • You can perform an exact match and an approximate match.
  • While performing an approximate match make sure to sort data in ascending order from left to right, and if data is not in ascending order then it would return an inaccurate result.
  • If range_lookup is true or omitted, it will perform a non-exact match but return an exact match if the lookup value exists in the lookup range.
  • If range_lookup is true or omitted, and the lookup value is not in the lookup range, it will return the nearest value which is less than the lookup value.
  • If range_lookup is false, then there is no need to sort data range.

Example

In the below example, we have used the HLOOKUP function with MATCH to create a dynamic formula and then we have used a drop-down list to change the lookup value from the cell.

excel-hlookup-function-example-1

The zone name from cell C7 is used as a lookup value. Range B1: F5 as table array and for row_index_num we have used match function to get the row number.

excel-hlookup-function-example-2

Whenever you change the value in cell C9, it will return the row number from the table array. You don’t have to change your formula again and again. Just change values with the drop-down list and you will get value for that.

8. HYPERLINK Function

The HYPERLINK function returns a string with a hyperlink attached to it. In simple words, like the HYPERLINK option you have in Excel, the HYPERLINK function helps you to create a hyperlink.

Syntax

HYPERLINK(link_location,[friendly_name])

Arguments

  • Link_Location: The location for which you want to add a HYPERLINK. It can be further split into two terms.
    1. link: It can be an address of a cell or range of cells in the same worksheet or in any other worksheet or in any other workbook. We can also link a bookmark from a word document.
    2. location: It can be a link to a hard drive, a server using the UNC path, or any URL from the internet or intranet. (In Excel online you can only use web address for HYPERLINK function). You can insert a link to the function by inserting it as a text with quotation marks or by referring to a cell containing the link as a text. Make sure to use “HTTPS://” before a web address.
  • [friendly_name]: It is an optional part of this function. It acts as the face of the connecting link.
    1. You can use any type of text, number, or both.
    2. You also refer to a cell which contains the friendly_name.
    3. If you skip it, the function will use the link address to display.
    4. If friendly_name returns an error, the function will display error.

Notes

  • Link a file saved on a Web Address: You can use a file that is saved on a web address. This helps us to share the file in an effective way.
  • Link a file saved on a Hard Drive: You can also use this function while working offline. You can link a file that is stored on your hard drive and access them through your single excel sheet, no need to go to every single folder to open them.
  • Link a Word Document File: This is also an awesome feature of HYPERLINK function. You can link a Word document file or a specific place in word document file using a bookmark.
  • Link a file without using Friendly Name: If you want to show the actual link to the file or place to the user. In this situation, you just need to skip the friendly name declaration in the HYPERLINK Function.

9. INDIRECT Function

The INDIRECT function returns a valid reference from a text string which represents a cell reference. In simple words, you can refer to a cell range by using the cell address as a text value.

Syntax

INDIRECT(ref_text, [a1])

Arguments

  • ref_text: A text which represents the address of a cell, an address of a range of cells, a named range, or a table name. For example, A1, B10:B20, or MyRange.
  • [a1]: A number or a boolean value to represent the type of cell reference you are specifying in ref_text. For example, if you want to use A1 reference style use TRUE or 1 and if you want to use R1C1 reference style use FALSE or 0 for R1C reference style. And if you omit to specify the cell reference type, it will use A1 style as default.

Notes

  • When you referred to another workbook, that workbook should be opened.
  • If you insert a row or a column in the range which you have referred, INDIRECT will not update that reference.
  • If you want to insert text directly into the function you have to put it in double quotation marks or you can also refer to a cell that has the text you want to use as a reference.

Example

1. Reference to Another Worksheet

You can also refer to another worksheet using the INDIRECT and you have to insert the worksheet name in it. In the below example, we have used the indirect function to refer to another worksheet and have the sheet name in cell A2 and cell reference in cell B2.

excel-indirect-function-example-1

In cell C2, we have used the following formula to combine the text.

=INDIRECT(“‘”&A2&”‘!”&B2)

This combination creates a text which is used by the INDIRECT function to refer to the cell A1 in sheet1 and the best part is when you change the worksheet name or cell address the reference will automatically change.

Cell A1 in “Sheet1” has the value “Yes” and that’s why indirect returns the value “Yes”.

2. Reference to Another Workbook

You can also refer to another workbook, in the same way, we did for another worksheet. All you have to do is just add a workbook name in your text that you are using as a reference.

excel-indirect-function-example-2

In the above example, we have used the following formula to get the value from the cell A1 of the workbook “Book1”.

=INDIRECT(“[“&A2&”]”&B2&”!”&C2)

As we have the workbook name in cell “A2”, the worksheet name in cell “B2” and the cell name in cell “C2”. We have combined them to use as input text in an indirect function.

Note: While combining cell reference as a text make sure to follow the right reference structure.

3. Using Named Ranges

Yes, you can also refer to a named range using the indirect function. It’s just simple. Once you create a named range you have to enter that named range as a text in INDIRECT.

excel-indirect-function-example-3

In the above example, we have a drop-down in cell E1 which has a list of named ranges, and in cell E2 we have used that name. As range B2:B5 is named as “Quantity” and range C2:C5 is named “Amount”.

When you select a quantity from the drop-down indirect function instantly refers to the named range. And when you select the amount from the drop-down, you will have the sum of the cell range C2:C5.

11. LOOKUP Function

The LOOKUP function returns a value (which you are looking up) from a row, column, or from array. In simple words, you can look up a value, and LOOKUP will return that value if it’s there in that row, column, or array.

Syntax

LOOKUP(value, lookup_range, [result_range])

There are two types of LOOKUP functions.

  • Vector Form
  • Array Form

Arguments

  • value: The value that you want to search from a column or a row.
  • lookup_range: The column or row from which you want to lookup for the value.
  • [result_range]: The column or row from which you want to return a value. This is an optional argument.

Notes

  • Instead of using array form, it’s better to use VLOOKUP or HLOOKUP.

12. MATCH Function

The MATCH function returns the index number of the value from an array. In simple words, the MATCH function looks up a value in the list and returns the position number of that value in the list.

Syntax

MATCH (lookup_value, lookup_array, [match_type])

Arguments

  • lookup_value: The value whose position you want to get from a list of values.
  • lookup_array: The range of cell or an array contains values.
  • [match_type]: The number (-1, 0 & 1) to specify how excel look for the value from the list of values.
    1. If you use 1, it will return the largest value which is equal or less than the lookup value. The values in the list must be sorted in ascending order.
    2. If you use -1, it will return the smallest value which is equal or greater than the lookup value. The values in the list must be sorted in ascending order.
    3. If you use 0, it will return the exact match from the list.

Notes

  • You can use wildcard characters.
  • If there is no matching value in the list if will return #N/A.
  • The match function is non-case sensitive.

Example

In the below example, we have used 1 as match type and we are looking for value 5.

excel-match-function-example-1

As I have already mentioned if you use 1 in match type it returns the largest value which is equal or smaller than the lookup value. In the entire list, there are 3 values that are smaller than 5 and 4 is the highest in them.

That’s why in the result it has returned 3 which is the position of value 4.

13. OFFSET Function

The OFFSET function returns a reference to a range which is a specific number of rows and columns away from a cell or range of cells. In simple words, you can refer to a cell or a range of cells by using rows and columns from a starting cell.

Syntax

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

Arguments

  • reference: The reference from which you want to offset to start. It can be a cell or range of adjacent cells.
  • rows: The number of rows that tell OFFSET to move up or down from the reference. To go downward you need a positive number and for going upwards you need a negative number.
  • cols: The number of columns tells OFFSET to move to the left or right from the reference. To go right you need a positive number and for going left you need a negative number.
  • [height]: A number to specify the rows to include in the reference.
  • [width]: A number to specify the columns to include in the reference.

Notes

  • OFFSET is a “volatile” function, it recalculates whenever there is any change to a worksheet.
  • It displays the #REF! error value if the offset is outside the edge of the worksheet.
  • If height or width is omitted, the height and width of reference are used.

Example

In the below example, we have used SUM with OFFSET to create a dynamic range which sums the values from all the months for a particular product.

excel-offset-function-example-1

14. ROW Function

The ROW function returns the row number of the referred cell. In simple words, with the ROW function, you can get the row number of a cell and if you don’t refer to any cell then it returns the row number for the cell where you insert it.

Syntax

ROW([reference])

Arguments

  • reference: A cell reference or a range of cells for which you want to check the row number.

Notes

  • It will include all types of sheets (Chart Sheet, Worksheet or Macro Sheet).
  • You can refer to sheets even if they are visible, hidden or very hidden.
  • If you skip specifying any value in the function it will give you the sheet number of the sheet in which you have applied the function.
  • If you specify an invalid sheet name, it will return a #N/A.
  • If you specify an invalid sheet reference, it will return a #REF!.

Example

In the below example, we have used the row function check the row number of the same cell where we have used the function.

excel-row-function-example-1

In the below example, we have referred to another cell to get the row number of that cell.

excel-row-function-example-2

You can use the row function to create a serial number list in your worksheet. All you have to do is just enter row functions in a cell and drag it up to the cell you want to add serial numbers.

excel-row-function-example-3

15. ROWS Function

The ROWS function returns a count of the rows from the referred range. In simple words, with the ROWS function you can count how many rows are in the range you have referred to.

Syntax

ROWS(array)

Arguments

  • array: A cell reference or an array to check the number of rows.

Notes

  • You can also use a named range.
  • It is not concerned with the values in the cells, it will simply return the number of rows in a reference.

Example

In the below example, we have referred to a vertical range of 10 cells and it has returned 10 in the result as the range includes 10 rows.

excel rows function example

16. TRANSPOSE Function

The TRANSPOSE function changes the orientation of a range. In simple words, by using this function you can change the data from a row into a column and from a column into a row.

Syntax

TRANSPOSE (array)

Arguments

  • array: An array or a range you want to transpose.

Notes

  • You have to apply TRANSPOSE as an array function, using the same number of cells as you have in your source range by pressing Ctrl + Shift + Enter.
  • If you select cells less than source range, it will transpose data only for those cells.

Example

Here we need to transpose data from range B2:D4 to range G2 to I4:

excel-transpose-function-example-1

For this, first, we need to go to the cell G2 and select cell range up to I4.

excel-transpose-function-example-2

Next is to enter (=TRANSPOSE(B2:D4)) in cell G2 and press Ctrl+Shift+Enter.

excel-transpose-function-example-3

TRANSPOSE will convert the data from the rows into columns, and the formula which we have applied is an array formula, you cannot change a single cell from it.

excel-transpose-function-example-4

Last Updated: June 21, 2023