Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of data in Excel, understanding this method is worthwhile. In this lesson, we will demonstrate how to use the XLOOKUP function to significantly simplify your data search.

What exactly is Excel XLOOKUP formula?

The Excel XLOOKUP function is a member of the family of lookup and reference functions. It is one of the most helpful features of Microsoft’s famous spreadsheet program. XLOOKUP is the simplest method for finding particular data items inside a cell range. The items included inside a previously set cell range are formatted. Does this sound familiar? The VLOOKUP function likewise operates on this concept. However, the more versatile Excel-XLOOKUP function allows you to look up not just one, but several items. Additionally, you may search for values vertically and horizontally inside your sheet.

In practice, what does this mean? Consider the following scenario: you have a digital client database in the form of an Excel file and are seeking for the address and phone number of a certain individual. With XLOOKUP, you can now search for related items by name and instantly see the needed information. It makes no difference if the sought data are included in a column, row, or table on another page. This indicates that the XLOOKUP function supersedes not only the VLOOKUP but also the HLOOKUP functions.

XLOOKUP syntax Function

The syntax of the XLOOKUP function is identical to those of VLOOKUP and HLOOKUP. If you’ve ever used them, you’ll appreciate how much more convenient XLOOKUP is. The following is the Excel syntax for the XLOOKUP function:

=XLOOKUP(lookup value, lookup array, return array, [if not found], [match mode], [search mode])

The XLOOKUP function accepts up to six parameters, the values of which are shown below.

  • lookup value (required): the value you’re looking for.
  • lookup array (required): the array in which the lookup value should be located.
  • return array (required): the array from which you want to retrieve and return the values when the lookup value is discovered.
  • [if not found] (optional): If no match is discovered, this value is returned.
  • [match mode] (optional): This option specifies the sort of match that you are searching for. There are several ways to specify it:
  • 0 – It searches for an exact match, and the result must precisely match the lookup array value. When not specified, it is also set as the default.
  • -1 – It searches for an exact match and then returns to the next lower value.
  • 1 – It searches for an exact match and then advances to the next bigger number.
  • 2 – It performs partial matches using wildcards, where the characters *,?, and have particular significance.

[search mode] (optional): Used to specify the lookup array’s XLOOKUP search mode. There are several ways to specify the same thing:

  • 1– Begin the search with the first item. When nothing is supplied, it is defaulted to default.
  • -1 – Conducts a reverse search, beginning with the last item.
  • 2 – Conducts a binary search in the lookup array in which the data must be sorted ascending. If the data is not properly organized, it may result in mistakes or incorrect outcomes.
  • 2 – Conducts a binary search in the lookup array, sorting the data in ascending order. If the data is not properly organized, it may result in mistakes or incorrect outcomes.

XLOOKUP’s Advantages and Disadvantages In Excel, the XLOOKUP function retains some of its benefits over VLOOKUP and INDEX/MATCH. However, it does have certain drawbacks.

The XLOOKUP Function’s Advantages

  1. It operates in both vertical and horizontal directions.
  2. Three inputs are required, rather than the four required by the VLOOKUP and INDEX MATCH methods.
  3. By default, an exact match is used.
  4. Utilize wildcards to conduct partial match lookups.
  5. Can execute descending order lookups.

INDEX MATCH now uses a single function rather than two.

The XLOOKUP Function’s Drawbacks

  1. Optional arguments may seem complicated to novices.
  2. Can take longer when two ranges are selected and the spreadsheet has an excessive number of cells.
  3. When the lookup and array results are not the same length, an error is returned.
  4. Both lookup and return ranges must be remembered.

How to Use Excel’s XLOOKUP Function

The XLOOKUP function is comparable to Excel’s LOOKUP function. XLOOKUP may be used by simply specifying the cell references for the function to act on.

Alternatively, you may utilize the top-level “Formula bar” box to input the XLOOKUP function syntax.

What makes XLOOKUP superior?

XLOOKUP simplifies and reduces the likelihood of errors in Excel’s most frequently used formulas. Simply type =XLOOKUP(what you're looking for, the list, the result list) and you’ll receive the result (or a #N/A error if the value is not found).

By default, this function looks for an exact match: One of the drawbacks of VLOOKUP is that you must provide FALSE as the last argument in order to get the right result. XLOOKUP corrects this by defaulting to precise matches. If desired, you may alter the lookup behavior using the match mode option.

The fourth parameter is used to accommodate the value not found situation. To suppress errors in the majority of business cases, we are compelled to encapsulate our lookup formulae with IFERROR or IFNA formulas. XLOOKUP has a fourth argument (described in further detail below) that allows you to choose the default output to use if your value is not found.

XLOOKUP includes extra parameters for searching for unusual circumstances. You may search from the top or bottom of a list, use wildcards, or use quicker alternatives for searching sorted lists.

It outputs a reference, not the value. While this may seem insignificant to casual Excel users, professional Excel users’ eyes light up when they find a formula that returns references. That is, you may mix XLOOKUP results with other formulae in novel ways.

It’s so much cooler to type; all you have to do is type =XL. I’m not sure whether this is a lucky coincidence, but saying =XL generates this formula.

Related Functions

  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

Excel UNIQUE Function

The guide demonstrates how to use the UNIQUE function and dynamic arrays in Excel to create unique values. You’ll learn how to use a basic formula to locate unique values inside a column or row, across many columns, and depending on criteria, among other things.

Excel 365’s introduction of the Excel UNIQUE formula altered everything! What was once considered rocket science becomes as simple as ABC. Now, you don’t need to be an expert in formulas to extract unique values from a range based on one or more criteria and organize the results alphabetically. All of this is accomplished via the use of straightforward formulae that anybody can understand and adapt to their own requirements.

Notes on usage

The Excel UNIQUE function returns a list of values that are unique inside a range or array. As a consequence, a dynamic array of unique values is created. If this array is the end output (i.e. it is not passed to another function), array contents “spill” onto the worksheet into a range that updates automatically when new unique values are added or deleted from the source range.

The UNIQUE function accepts three arguments: an array, a by col parameter, and an exactly once argument. The first parameter, array, specifies the array or range from which unique values should be extracted. This is the only argument that is necessary. The second input, by col, specifies whether unique data are extracted by rows or columns. UNIQUE extracts unique values from rows by default. Set by col to TRUE or 1 to compel UNIQUE to extract unique data by column. The last option, exactly once, specifies the behavior for values that occur many times. By default, UNIQUE extracts all unique values, regardless of their number of appearances in the array. Set exactly once to TRUE or 1 to retrieve unique values that exist just once in the array.

 

UNIQUE function in Excel

In Excel, the UNIQUE function produces a list of distinct values from a range or array. It works with any form of data, including text, numbers, dates, and timings.

The function falls within the category of Dynamic Arrays functions. As a consequence, a dynamic array is created that naturally overflows onto adjacent cells, either vertically or horizontally.

excel unique function1

The Excel UNIQUE function has the following syntax:

=UNIQUE(array, [by col], [once exactly])

Where:

  • Array (needed) – the range or array from which unique values are to be returned.
  • By col (optional) – a logical value specifying how data should be compared:
  • TRUE – does cross-column comparisons.
  • FALSE or omitted (default) – does cross-row comparisons.
  • Exactly once (optional) – a logical value defining which values are regarded as unique:
  • TRUE – yields values that only occur once, which corresponds to the database concept of uniqueness.
  • FALSE or omitted (default) – returns the range or array’s distinct (different) values.

 

Important note:

At the moment, the UNIQUE feature is only accessible in Microsoft Excel 365 and Excel 2021. Excel 2019, 2016, and older versions do not allow dynamic array formulae, and so do not have the UNIQUE function.

Related Functions

  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Excel SORT Function

The excel sort function is one of the most useful new features in Excel. It is one of many functions that make advantage of Excel’s new dynamic array calculation engine, which enables Excel to spill results from a single formula across numerous cells.

SORT function in Excel

The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.

SORT is a member of the family of dynamic array functions. As a consequence, a dynamic array is created that overflows vertically or horizontally onto nearby cells, depending on the form of the source array.

The SORT function has the following syntax:

=SORT(array, [sort index], [sort order], [by col])

Where:

  • Array (needed) – represents a collection of values or a range of cells to sort. These may be any kind of value, such as text, numbers, dates, or timings.
  • Sort index (optional) – an integer indicating the column or row from which to sort. If this parameter is missing, the default index 1 is used.
  • Sort order (optional) – specifies the order in which items are sorted:
  • 1 or omitted (default) – ascending order, that is, from smallest to greatest -1 – descending order, that is, from greatest to smallest
  • By col (optional) – a logical value indicating the sort direction:
  • FALSE or omitted (by default) – row-based sorting. This is the most often used option.
  • TRUE – column-based sorting. This option is appropriate if your data is structured horizontally in columns.

Notes on use

The excel sort formula uses a formula to organize the contents of a range or array in ascending or descending order. The outcome of SORT is a dynamic array of data that “spill” into a range on the worksheet. If the values in the source data change, the SORT result will immediately update.

The SORT function accepts four parameters: an array, a sort index, a sort order, and a by col parameter. The first option, array, specifies the array or range to sort. This is the only argument that is necessary. By default, the SORT method sorts data upward using the array’s first column. Utilize the optional sort index and sort order parameters to specify which column to sort by and in what order to sort (ascending or descending). Sort index should be an integer that corresponds to the sorting column (or row). For instance, to sort by the third column in a set of data, use the sort index 3 parameter. The optional sort order parameter specifies the direction of the sort. Ascending order is 1 and descending order is -1. The SORT function by default sorts data vertically by row. Set the fourth option, by col, to TRUE to sort a range horizontally by columns.

Excel sort formula – Tips and Tricks

Excel sort function is a novel dynamic array function, and as such, it has a few quirks worth noting:

Currently, only Microsoft 365 and Excel 2021 support the excel sort function. Because Excel 2019 and Excel 2016 do not support dynamic array formula, they do not have the SORT function.

If the array generated by a SORT formula is not provided to another function, Excel dynamically builds and populates a suitably sized range with the sorted data. As a result, always ensure that there are sufficient empty cells below or to the right of the cell where the formula is entered; otherwise, a #SPILL error occurs.

The findings are continuously updated in real time as the underlying data changes. The array given to the formula, on the other hand, does not immediately expand to accept additional elements inserted outside of the referred array. To include such things, you must either change the array reference in your formula, convert the source range to a table, or build a dynamic named range.

Excel’s SORT Function Constraints

The SORT function cannot be used to sort columns or rows that are not contiguous.

For instance, you cannot sort the employee names and wages separately since they are not adjacent.

However, you may sort by employee name and date of hire, or by employee name and date of hire and salary.

If you ever need to sort non-adjacent columns, you may use the approach outlined in this article.

If you wish to sort any range of cells according to a row or column outside that range, you cannot use the SORT function.

For instance, you cannot use the SORT function to sort just the employee names and joining dates by salary.

To resolve these sorts of issues, you may utilize Excel‘s SORTBY function. Alternatively, follow the process outlined in this article.

Arrange and Rank

In this article, we’ll examine one Dynamic Array Functions: SORT. It is excellent for showing a list in a ranked or chronological sequence (date, time).

In our example, we’re going to classify workers according to the amount of time they spend in the office (duration) over the course of a week. This will rank the workers from those who spent the greatest time at work to those who spent the least.

However, before we begin sorting, allow me to demonstrate quickly how I combined all of the duration periods for each employee using the SUMIF function.

The SORT Feature

Along with ordering the personnel from most to least hours worked, it would be beneficial to provide the matching hours next to each name. There are other methods to achieve this, however we will utilize the SORT function. This is another Dynamic Array Function that returns a wide range of values.

To use SORT, you only need to provide the range to sort and the order in which it should be sorted. (The SORT function’s second parameter, sort index, is not required in this case.)

Our straightforward formula would be as follows:

=SORT(B2:B8,,-1)

excel sort function1

Related Functions


  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Excel TRANSPOSE Function

Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel. Its goal, as a component of the Excel lookup and reference functions, is to arrange data in the appropriate manner. To run the formula, pick the precise size of the transposed range and hit the CSE key (“Control+Shift+Enter“).

Description


The TRANSPOSE function in Microsoft Excel returns a transposed range of cells. For instance, if a vertical range is specified as a parameter, a horizontal range of cells is returned. Alternatively, if a horizontal range of cells is specified as a parameter, a vertical range of cells is returned.

Excel TRANSPOSE formula is an Excel built-in function that is classified as a Lookup/Reference Function. It may be used in Excel as a worksheet function (WS). The TRANSPOSE function is a worksheet function that may be used as part of a formula in a worksheet cell.

Syntax


The Excel TRANSPOSE Function in Microsoft Excel has the following syntax:

=TRANSLATE (range)

Arguments or Parameters:

range – The range of cells to transpose.

Returns


TRANSPOSE returns a range of cells that have been transposed.

Note:

The TRANSPOSE function requires an array as the range value. Enter a value and then press Ctrl+Shift+Enter to create an array. This will enclose the formula in brackets, indicating that it is an array.

Applicable To


 Office 365

Notes on use:

  • The TRANSPOSE function transforms a vertical range of cells to a horizontal range of cells, or vice versa. TRANSPOSE, in other terms, “flips” the orientation of a specified range or array:
  • TRANSPOSE translates a vertical range to a horizontal range when provided one.
  • TRANSPOSE transforms a horizontal range to a vertical range when provided one.
  • When an array is transposed, the first row becomes the new array’s first column, the second row becomes the new array’s second column, the third row becomes the new array’s third column, and so on.
  • TRANSPOSE is a function that works with both ranges and arrays. Ranges that have been transposed are dynamic. If the data in the source range changes, TRANSPOSE updates the data in the target range instantly.

Excel TRANSPOSE Function


The TRANSPOSE Excel function, as the name implies, may be used to transpose data in Excel.

The syntax is as follows:

=TRANSPOSE(array)

The array denotes a range of cells to be transposed in this case.

Take the following basic steps:

Step1: Choose the range of cells in which you wish the output data to be transposed.

Step2: TRANSPOSE is an array formula; hence, the precise amount of cells must be specified.

Step3: If the range of your table is 6×2, i.e., 6 rows and 2 columns, you must pick a 2×6 range for the transposed data, i.e., 2rows and 6 columns.

excel transpose function1

Step4: Insert the formula =TRANSPOSE (A1:F5)

excel transpose function1

Step5: Avoid pressing Enter! Ctrl+Shift+Enter is required to perform array formula.

Step6: It’s worth noting that transpose is an array formula. An array formula must be input by hitting the Ctrl+Shift+Enter key combination, which causes the formula bar to insert a set of braces around the formula.

Step7: This instructs Excel to print an array of data rather than a single cell.

Step8: Transposition of the data is now complete.

excel transpose function1

Excel Vlookup Function

This post will guide you how to use Excel VLOOKUP function with syntax and examples in Microsoft excel.

Description

The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.

The VLOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The VLOOKUP function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the VLOOKUP function is as below:

= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])

Where the VLOOKUP function arguments are:

  • Lookup_value -This is a required argument. The value that you want to search for, in the first column of the table. Note: the lookup_value can be a value, a cell or ranges reference, or a text string.
  • Table_array – This is a required argument. Two or more columns of data to be searched in the first column.
  • column_index_num – This is a required argument.  The column number in table_array.
  • Range_lookup – This is a optional argument.  The value can be set as True or False. If true, the function will return an approximate match value. If False, it will find an exact match or it will return an error value #N/A.

Example

The below examples will show you how to use Excel VLOOKUP Lookup and Reference Function.

#1 To search “39” text string in the first column and returns the value from column 2 that is in the same row, just using the following excel formula: =VLOOKUP(39,A1:C4,2,FALSE)

excel vlookup function example1


Related Formulas

  • Check If a Value Exists in a Range
    This post will guide you how to use VLOOKUP function to check if a value exists in a given range of cells in Excel. How to check if a specified value exists in a range and then return the value in the adjacent cell…
  • VLOOKUP Values from Multiple Worksheets
    you can use the VLOOKUP function to find the values from multiple worksheets in Excel. For example, assuming that you have two worksheets, and you want to search the data across those two worksheet in your current worksheet (sheet3)……
  • Excel Vlookup Return True or False
    you can use the VLOOKUP function to look for a value in a column in a table and then returns TRUE from a given column in that table if it finds something. If it doesn’t, it returns FALSE …
  • VLOOKUP Return Multiple Values Horizontally
    You can create a complex array formula based on the INDEX function, the SMALL function, the IF function, the ROW function and the COLUMN function to vlookup a value and then return multiple corresponding values horizontally in Excel.…

Excel Rows Function

This post will guide you how to use Excel ROWS function with syntax and examples in Microsoft excel.

Description

The Excel ROWS function returns the number of rows in a cell reference.

The ROWS function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The ROWS function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the ROWS function is as below:

= ROWS(array)

Where the ROWS function arguments is:
array-This is an required argument. An array, or A reference to a range of cells.

Example

The below examples will show you how to use Excel ROWS Lookup and Reference Function to return the number of rows in a range of cells.

#1 To get the number of rows in the cell range “A1:C4”, just using the following excel formula: =ROWS(A1:C4)

excel rows function example1


More Excel Rows Examples

  • Flip a Column of Data Vertically
    How do I remove time part from a date in Excel. How to get only date part from the date with time values via Format cells feature or Excel function. How to extract date from a date and time with VBA code in Excel….
  • Randomly Select Cells
    If you want to randomly select cells from a range of cells, you can use a formula based on the INDEX function, the RANDBETWEEN function and the Rows function…..
  • List all Worksheet Names
    Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you..…
  • Split Data in One Column to Multiple Columns
    If you want to split each sets in one column from row to row in stacked columns, and you need to create a formula based on the OFFSET function, the COLUMNS function, and the ROWS function..…
  • Copy and Paste Only Non-blank Cells
    If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet..…

Excel Row Function

This post will guide you how to use Excel ROW function with syntax and examples in Microsoft excel.

Description

The Excel ROW function returns the row number of a cell reference.

The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The ROW function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the ROW function is as below:

= ROW ([reference])

Where the ROW function arguments is:
Reference – This is an Optional argument. the cell or range of cells reference that you want to get the row number.

Note: If the reference is omitted, the ROW function will return the row number of the current cell.

Excel Row Function Examples

The below examples will show you how to use Excel ROW Lookup and Reference Function to return the row number of a cell reference.
#1 To get the row number of “B5” cell in B1 Cell, just using the following excel formula: =ROW(B5)

excel row function example1

#2 To get the row number of the current cell, just using excel formula: =ROW()

excel row function example2

#3 get the first row number of range in excel

You can get the first row number in a range with an excel formula based on the ROW function as follows:

=ROW(Range)
=MIN(ROW(Range))

The ROW function will only display the first row number.

excel row function example3

Related Functions


  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

More Excel ROW Formula Examples


  • Get the Position of the nth Occurrence of a Character in a Cell
    If you want to get the position of the nth occurrence of a character using a excel formula, you can use the FIND function in combination with the SUBSTITUTE function.
  •  Get the position of Last Occurrence of a character or string in a cell
    If you want to get the position of the last occurrence of a character in a cell, then you can use a combination of the LOOKUP function, the MID function, the ROW function, the INDIRECT function and the LEN function to create an excel formula.…
  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Get the First Row Number in a Range
    If the ROW function use a Range as its argument, it only returns the first row number.You can also use the ROW function within the MIN function to get the first row number in a range. You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range.…
  •  Get the Last Row Number in a Range
    If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…
  • Get the position of Last Occurrence of a value in a column
    If you want to find the position number of the last occurrence of a specific value in a column (a single range), you can use an array formula with a combination of the MAX function, IF function, ROW function and INDEX Function.
  • Get the position of nth occurrence of a value in column
    If you want to locate the second, third or nth occurrence of a specified value, you can use a combination of the INDEX function, the IF function, ROW function and SMALL function to create a new excel array formula.
  • Split Text String to an Array
    If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string…
  • Three Ways to Get the Last Row Number in a Range
    If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range…
  • Three Ways to get the First Row Number in a Range
    you can use ROW function to get the first row number in a range directly. You can also use the ROW function within the MIN function to get the first row number in a range.You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range…
  • Transpose Multiple Columns into One Column
    You can use the following excel formula to transpose multiple columns that contain a range of data into a single column, and you can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly…
  • Delete Every Other Row
    To delete every other row in Excel, you need to filter alternate rows, and then select those rows, and delete them at once. So you need to create a helper column using MOD function with ROW function…
  • Sum Numbers from 1 to N
    Assuming that you supply a number 100, and you want to sum the numbers from 1 to 100 (1+2+3+…+100), you need to create a formula based on the SUMPRODUCT function, the ROW function and the INDIRECT function to achieve the result..….
  • Split Data in One Column to Multiple Columns
    If you want to split each sets in one column from row to row in stacked columns, and you need to create a formula based on the OFFSET function, the COLUMNS function, and the ROWS function..…
  • VLOOKUP Return Multiple Values Horizontally
    You can create a complex array formula based on the INDEX function, the SMALL function, the IF function, the ROW function and the COLUMN function to vlookup a value and then return multiple corresponding values horizontally in Excel.…
  • Copy and Paste Only Non-blank Cells
    If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet..…
  • Sum Every Nth Row or Column
    If you want to sum every nth rows in Excel, you can create an Excel Array formula based on the SUM function, the MOD function and the ROW function..….
  • Generate All Possible Combinations of Two Lists
    You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function to get a list of all possible combinations from those two list….
  • Find Missing Numbers in a Sequence in Excel
    You can use an excel array formula based on the SMALL function, the IF function, the ISNA function, the MATCH function, and the ROW function to find missing numbers in a sequence…

 

 

Excel Match Function

This post will guide you how to use Excel MATCH function with syntax and examples in Microsoft excel.

Description

The Excel MATCH function search a value in an array and returns the position of that item.

The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The MATCH function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the MATCH function is as below:

= MATCH  (lookup_value, lookup_array, [match_type])

Where the MATCH  function arguments are:
Lookup_value -This is a required argument.  The value that you want to search.
Lookup_array – This is a required argument.  The data array that is to be searched.
Match_type – This is an optional argument.  This value can be set as: 1, 0, -1. The default value is 1.

Note:  
1 – the MATCH function will search the largest value that is less than or equal to Lookup_value
0 – The MATCH function will search the first value that is exactly equal to Lookup_value
-1 – The Match function will search the smallest value that is greater than or equal to Lookup_value.

Example

The below examples will show you how to use Excel MATCH  Lookup and Reference Function .

#1 =MATCH(35,A1:A4,1)

This Excel Formula will lookup the value “35” and find the largest value that is less than or equal to “35”, it will return “2”, As the value “34” is the only  largest value that is less than to “35”. So it will return the position of value “34” in range “A1:A4”.

excel match function example1

#2 =MATCH(35,A1:A4,0)

This Formula will return the “#N/A” value, As the Match type is set to “0”, it means that the function will find the first value that is exactly equal to “35”, but it is not able to find the value “35” in “A1:A4” range.

excel match function example2

#3 =MATCH(35,A1:A4,-1)

This Formula will return “2”, if the Match type is set to “-1”, the Match function will find the smallest value that is greater than or equal to “35”in range “A1:A4”, then return its position.

excel match function example3

More Excel MATCH Formula Examples


  • Finding the Max and Min value in an Alphanumeric Data
    if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.….
  • Convert Weekday Name to Number
    Assuming that you have list of data B1:B7 that contain weekday names, you want to convert all weekday names to numbers.You can use a formula based on the MATCH function to achieve the result
  • Find Missing Numbers in a Sequence in Excel
    You can use an excel array formula based on the SMALL function, the IF function, the ISNA function, the MATCH function, and the ROW function to find missing numbers in a sequence…
  • Find Closest Value or Nearest Value in a Range
    You need to use an excel array formula based on the INDEX function, the MATCH function, the MIN function and the ABS function to find Closest Value or Nearest Value in a Range in Excel…

Excel Lookup Function

This post will guide you how to use Excel LOOKUP function with syntax and examples in Microsoft excel.

Description

The Excel LOOKUP function will search a value in a vector or array.

The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The LOOKUP function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the LOOKUP function is as below:

= LOOKUP (lookup_value, lookup_vector, [result_vector])

Where the LOOKUP function arguments are:
Lookup_value -This is a required argument.  A value that you want to search in the lookup_vector.
Lookup_vector – This is a required argument.  A excel Range that only contains one row or one column.
Result_vector – This is an optional argument. A excel Range that only contains one row or one column. It must be the same size as Lookup_vector. The lookup function will look up the value in the lookup_value range and returns the value from the same position in the result_vector.
Note: if Result_vector is omitted, the Lookup function will return the first column data.

Example

The below examples will show you how to use Excel LOOKUP Lookup and Reference Function to search a value from a vector or array.

#1 To look up the value “34”in  A1:A2 range, then get the value from the same row or column in B1:B2 range, just using the following excel formula: =LOOKUP(“34”,A1:A2,B1:B2)

excel lookup function example 1

More Excel LOOKUP Formula Examples


  • Finding the Max and Min value in an Alphanumeric Data
    if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.….
  • Convert Weekday Name to Number
    Assuming that you have list of data B1:B7 that contain weekday names, you want to convert all weekday names to numbers.You can use a formula based on the MATCH function to achieve the result
  • List all Worksheet Names
    Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you..…
  • Copy and Paste Only Non-blank Cells
    If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet..…

Excel Indirect Function

This post will guide you how to use Excel INDIRECT function with syntax and examples in Microsoft excel.

Description

The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference.

The INDIRECT function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The INDIRECT function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the INDIRECT function is as below:

= INDIRECT  (ref_text,[a1])

Where the INDIRECT function arguments are:
Ref_text -This is a required argument. A reference to a cell as a text string.
A1 – This is an optional argument.  It is a logical value that specify the style of the Ref_text reference.
Note: If A1 value is True or omitted, the ref_text will be interpreted as an A1-style reference.
If A1 value is False, The ref_text will be interpreted as R1C1 style reference.

Example

The below examples will show you how to use Excel INDIRECT Lookup and Reference Function to return a  cell or range reference that is represented by a text string.

#1 To get the value of the reference in cell A2, just using the following excel formula: =INDIRECT(A2)

excel indirect function example1

More Excel INDIRECT  Function Examples


  • Get Cell Value Based on Row and Column Numbers
    If you want to retrieve values using cell references based on row and column numbers, you can create a formula based on the INDIRECT function and the ADDRESS function.….
  • Sumif with 3D Reference for Multiple Worksheet
    If you want to sum 3d references across the different worksheet, and you need to create a complex formula based on the SUMPRODUCT function, the SUMIF function, and the INDIRECT function..….
  • Sum Numbers from 1 to N
    Assuming that you supply a number 100, and you want to sum the numbers from 1 to 100 (1+2+3+…+100), you need to create a formula based on the SUMPRODUCT function, the ROW function and the INDIRECT function to achieve the result..….

Excel Index Function

This post will guide you how to use Excel INDEX function with syntax and examples in Microsoft excel.

Description

The Excel INDEX function returns a value from a table based on the index (row number and column number). You can use INDEX function to extract entire rows or entire columns. This function is used to combine with the MATCH function to lookup value in a range or array.

The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.
The INDEX function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

There are two ways to use the INDEX function in excel:

If you want to get the value of a specified cell or array of cell, you can refer to Array form.

If you want to get a reference to specified cells, you can refer to Reference form.

The syntax of the INDEX function is as below:

= INDEX (array, row_num,[column_num])      #Array form
=INDEX(reference, row_num,[column_num],[area_num)     #Reference form

The array form is used in most cases, and if the first argument of the INDEX function is an array constant, you need to use the array form. If you want to perform a three-way lookup in a range, you can use the reference form.

Where the INDEX function arguments are:

  • array -This is a required argument. A range of cells or data array. If array contains only one row or column, the corresponding row_num or Column_num argument is optional.
  • Row_num – The row number in data array. If Row_num is omitted, column_num is required.
  • Column_num – The column position in data array. If Column_num is omitted, row_num is required.
  • Area_num – it is set as a number. If the first argument point to more cell ranges, if area_num is set to 1, then the first area will be selected.

Note:

  • If you set the row_num and column_num at the same time, the INDEX function will return the value in the cell at the intersection of row number and column number.
  • If you set the value of row_num or column_num to 0, the INDEX function will return the array of values for the entire row or column in the array data.
  • Row_num and Column_num must point to a cell within array data, if not, the index function returns #REF!

Excel INDEX Function Example

The below examples will show you how to use Excel INDEX Lookup and Reference Function to return a value from a table based on the intersection of row number and column number.

#1 To get the value at the intersection of the second row and second column in the table array: A1:C2, just using the following excel formula:

=INDEX(A1:C2,2,1)

excel index function example1

#2 use reference form to extract a value form the second area of C2:D5. Using the following formula.

=INDEX((B1:C5,C2:D5),2,2,2)
excel index function example2

You will see that the intersection of the second row and second column in the second area of C2:D5, which is the contents of cell D3. It returns 149.

More Excel INDEX Formula Examples


  • Get the First Match in Two Excel Ranges
    If you want to find the first match between two excel ranges, you can use a combination of the INDEX function, the MATCH function and COUNTIF function to create a new formula….
  • Extract a List of Unique Values from a Column Range
    f you want to extract a list of unique items from a column or range, you can use a combination of the IFERROR function, the INDEX function, the MATCH function and the COUNTIF function to create an array formula…..
  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Get the Last Row Number in a Range
    If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…
  • Get the First Row Number in a Range
    If the ROW function use a Range as its argument, it only returns the first row number.You can also use the ROW function within the MIN function to get the first row number in a range. You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range.…
  • Get the position of Last Occurrence of a value in a column
    If you want to find the position number of the last occurrence of a specific value in a column (a single range), you can use an array formula with a combination of the MAX function, IF function, ROW function and INDEX Function.
  • Lookup the Next Largest Value
    If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula. You can use the following formula:=INDEX(A2:A5,MATCH(200,A2:A5)+1)…
  • Reverse a List or Range
    If you want to reverse a list or range, you can use a combination of the INDEX function, the COUNTA function, the ROW function or ROWS function to create a new formula. you can use the following formula:=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)…
  • Lookup the Value with Multiple Criteria
    If you want to lookup the value with multiple criteria in a range, you can use a combination with the INDEX function and MATCH function to create an array formula…
  • Transpose Values Based on the Multiple Lookup Criteria
    If you want to lookup the value with multiple criteria, and then transpose the last results, you can use the INDEX function with the MATCH function to create a new formula.…
  • Get nth Match with One Criteria using INDEX/MATCH
    if you want to find the 2th occurrence of the member “jenny” in the range B2:B10 and extracts its relative bonus value in the range D2:D10, you can used the following array formula:=INDEX(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))…
  • Find the nth Largest Value
    To get the 1st, 2nd, 3rd, or nth largest value in a range (single column, or row), you can use the LARGE function…
  • Find the nth Smallest Value
    You can use the SMALL function to get the 1st, 2nd, 3rd, or nth smallest value in an array or range. Also you can use the SMALL function within the INDEX function to extract the relative value of the same row…
  • Extract the Entire Column of a Matched Value
    If you want to lookup value in a range and then retrieve the entire row, you can use a combination of the INDEX function and the MATCH function to create a new excel formula..…
  • Lookup Entire Row using INDEX/MATCH
    If you want to lookup entire row and then return all values of the matched row, you can use a combination of the INDEX function and the MATCH function to create a new excel array formula.
  • Find nth Occurrence with Multiple Criteria Using INDEX/MATCH
    If you want to find the nth occurrence with multiple criteria, you can use a combination with the INDEX function, SMALL function, nested IF function and ROW function to create a complex excel formula like this:=INDEX(Array,SMALL(IF(Range1…
  • Two-way Lookup Formula
    If you want to look up a value in a table using both rows and columns, you can use a combination with the INDEX function and the MATCH function to create an excel formula.…
  • SUM a Column with Lookup value in a Range
    If you want to lookup value in a range and then retrieve the entire row, you can use the MATCH function within the INDEX function…
  • Get Cell Address of a Lookup Value
    If you want to lookup a value in a range or column and return the cell address of the first match of lookup value, you can use a combination with the CELL function, the INDEX function and the MATCH function….
  • Transpose Multiple Columns into One Column
    You can use the following excel formula to transpose multiple columns that contain a range of data into a single column, and you can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly…
  • Flip a Column of Data Vertically
    How do I remove time part from a date in Excel. How to get only date part from the date with time values via Format cells feature or Excel function. How to extract date from a date and time with VBA code in Excel….
  • Finding the Max and Min value in an Alphanumeric Data
    if you want to find the Maximal or minimal string value from an alphanumeric data list in the range B1:B5, you can create a formula based on the LOOKUP function and the COUNTIF function.….
  • Sum Specific Row or Column in Named Range
    Assuming that you have a name range “excelhow”, if you want to sum a specific row (row 2) in named range, you need to create a formula based on the SUM function and the OFFSET function and the COLUMNS function to achieve the reuslt….
  • Randomly Select Cells
    If you want to randomly select cells from a range of cells, you can use a formula based on the INDEX function, the RANDBETWEEN function and the Rows function…..
  • List all Worksheet Names
    Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you..…
  • Split Data in One Column to Multiple Columns
    If you want to split each sets in one column from row to row in stacked columns, and you need to create a formula based on the OFFSET function, the COLUMNS function, and the ROWS function..…
  • VLOOKUP Return Multiple Values Horizontally
    You can create a complex array formula based on the INDEX function, the SMALL function, the IF function, the ROW function and the COLUMN function to vlookup a value and then return multiple corresponding values horizontally in Excel.…
  • Copy and Paste Only Non-blank Cells
    If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet..…
  • Generate All Possible Combinations of Two Lists
    You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function to get a list of all possible combinations from those two list….
  • Find Closest Value or Nearest Value in a Range
    You need to use an excel array formula based on the INDEX function, the MATCH function, the MIN function and the ABS function to find Closest Value or Nearest Value in a Range in Excel…

 

Excel Hyperlink Function

This post will guide you how to use Excel HYPERLINK function with syntax and examples in Microsoft excel.

Description

The Excel HYPERLINK function creates a shortcut/hyperlink to a document, when you click this hyperlink, the excel will open the file that is stored on a network server or local location.

​The HYPERLINK function is a build-in function in Microsoft Excel and it is categorized as a Lookup and reference Function.

The HYPERLINK function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the HYPERLINK function is as below:

= HYPERLINK(link_location,[friendly_name])

Where the HYPERLINK function arguments are:
Link_location -This is a required argument. A link path to a file that you want to hyperlink.
Friendly_name-This is an optional argument. The text string to display in the excel cell.
Note: If the link_location does not exist or cannot be navigated, error will appear when you click the hyperlink text.

Example

The below examples will show you how to use Excel HYPERLINK Lookup function to create a hyperlink text to a file.

#1 To create a hyperlink text with “excelhow.net” friendly name, with “http://www.excelhow.net” link as link_Location in B1 cell , just using the following formula:=HYPERLINK(“https://www.excelhow.net”,”excelhow.net”).

excel hyperlink function example1

Excel Hlookup Function

This post will guide you how to use Excel HLOOKUP function with syntax and examples in Microsoft excel.

Description

The Excel HLOOKUP function lookup a value in the top row of the table and return the value in the same column based on index_num position.

The HLOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The HLOOKUP function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the HLOOKUP function is as below:

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

Where the HLOOKUP function arguments are:
Lookup_value -This is a required argument. The value that you want to search for, in the first row of the table. Note: the lookup_value can be a value, a cell or ranges reference, or a text string.
Table_array – This is a required argument. Two or more rows of data to be searched in the top row.
Row_index_num – This is a required argument.  The row number in table_array.
Range_lookup – This is a optional argument.  The value can be set as True or False. If true, the function will return an approximate match value. If False, it will find an exact match or it will return an error value #N/A.

Example

The below examples will show you how to use Excel HLOOKUP Lookup and Reference Function.

#1 To search “two” text string in row 1 and returns the value from row 2 that is in the same column, just using the following excel formula: =HLOOKUP(“two”,A1:C2,2,TRUE)

excel hlookup function example1

Excel Columns Function

This post will guide you how to use Excel COLUMNS function with syntax and examples in Microsoft excel.

Description

The Excel COLUMNS function returns the number of columns in an Array or a reference.

The COLUMNS function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The COLUMNS function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the COLUMNS function is as below:

=COLUMNS (array)

Where the COLUMNS function arguments is:

  • Array -This is a required argument.  A reference to a cell or a range of cells.

Example

The below examples will show you how to use Excel COLUMNS Lookup and Reference Function to return the number of columns in a given range.

#1 To get the number of columns in the reference D1:F5, just using the following excel formula:

=COLUMNS(D1:F5)


More Excel COLUMNS Formula Examples

  • Transpose Multiple Columns into One Column
    You can use the following excel formula to transpose multiple columns that contain a range of data into a single column, and you can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly…
  • Sum Specific Row or Column in Named Range
    Assuming that you have a name range “excelhow”, if you want to sum a specific row (row 2) in named range, you need to create a formula based on the SUM function and the OFFSET function and the COLUMNS function to achieve the reuslt….
  • Split Data in One Column to Multiple Columns
    If you want to split each sets in one column from row to row in stacked columns, and you need to create a formula based on the OFFSET function, the COLUMNS function, and the ROWS function..…

Excel Column Function

This post will guide you how to use Excel COLUMN function with syntax and examples in Microsoft excel.

Description

The Excel COLUMN function returns the first column number of the given cell reference.

The COLUMN function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The COLUMN function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the COLUMN function is as below:

=COLUMN ([reference])

Where the COLUMN function arguments is:
Reference -This is an optional argument.  A reference to a cell or a range of cells for which you want to get the first column number.
Note: If the Array argument is omitted, the Excel COLUMN function will return the column number of the cell that the function is entered in.

Example

The below examples will show you how to use Excel COLUMN Lookup and Reference Function to return the column number of a cell reference.

#1 To get the number of column in B1 Cell, just using the following excel formula: =COLUMN ( )

excel columns function example1

#2 To get the number of column in the reference D1:F5, just using the following excel formula: =COLUMN(D1:F5)

excel columns function example2

More Excel Column Function Examples


  • VLOOKUP Return Multiple Values Horizontally
    You can create a complex array formula based on the INDEX function, the SMALL function, the IF function, the ROW function and the COLUMN function to vlookup a value and then return multiple corresponding values horizontally in Excel.…
  • Sum Every Nth Row or Column
    If you want to sum every nth rows in Excel, you can create an Excel Array formula based on the SUM function, the MOD function and the ROW function..….

Excel Choose Function

This post will guide you how to use Excel CHOOSE function with syntax and examples in Microsoft excel.

Description

The Excel CHOOSE function returns a value from a list of values.

The CHOOSE function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The CHOOSE function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the CHOOSE function is as below:

=CHOOSE (index_num, value1,[value2],…)

Where the CHOOSE function arguments are:
index_num -This is a required argument. Specify the position number in the list of values.
Value1,[value2] – This is a required argument.  A list of one or more values that you want to return
Note: The index_num value must be a number between 1 and 29.
If index_num value is less than 1 or greater than the length of the value list, the CHOOSE function will return #VALUE! Error value.

Example

The below examples will show you how to use Excel CHOOSE Lookup and Reference Function to return a value from a value list based on a position value.

#1 To get the second value in the value list in B1 Cell, just using the following excel formula: =CHOOSE(2,A1,A2,A3)

excel choose function example1

More Excel Choose Function Examples


  • Get the First Monday of a Given Year
    To calculate the first Monday of the year or given any date, you can create a new complex formula based on the DATE function, the YEAR function and the WEEKDAY function……
  • List all Worksheet Names
    Assuming that you have a workbook that has hundreds of worksheets and you want to get a list of all the worksheet names in the current workbook. And the below will introduce 3 methods with you..…
  • Copy and Paste Only Non-blank Cells
    If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet..…

Excel Areas Function

This post will guide you how to use Excel AREAS function with syntax and examples in Microsoft excel.

Description

The Excel AREAS function returns the number of ranges in a reference.

The AREAS function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The AREAS function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the AREAS function is as below:

=AREAS (reference)

Where the AREAS function arguments are:
Reference -This is a required argument. A Excel cell reference or A range reference, it also can be a multiple ranges.

Example

The below examples will show you how to use Excel AREAS Lookup and Reference Function to return the number of ranges in a cell reference.

#1  Formula: =AREAS(B1:C3)

excel areas function example1

Note: the above excel formula will return the number of ranges in B1:C3 range cells.

#2 Formula: =AREAS((B1:C3,A2:B3))

excel areas function example2

Excel Address Function

This post will guide you how to use Excel ADDRESS function with syntax and examples in Microsoft excel.

Description

The Excel ADDRESS function returns a reference as a text string to a single cell.

The ADDRESS function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The ADDRESS function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the ADDRESS function is as below:

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

Where the ADDRESS function arguments are:

  • row_num -This is a required argument. The row number to use in the cell reference.
  • column_num – This is a required argument. The column number to use in the cell reference.
  • Abs_num – This is an optional argument. It will specify the type of reference to use. The following values can be used:

             1 – Absolute

             2 – Absolute row; relative column

             3 – Relative row; absolute column

             4 – Relative

A1 – This is an optional argument. It will specify the style of reference to use.

  • True– A1 reference style
  • False – R1C1 reference style

Sheet_text – This is a required argument. The sheet name to use.

Example

The below examples will show you how to use Excel ADDRESS Lookup and Reference Function to return a reference as a text.

#1 Absolute reference, type formula: =ADDRESS(3,4)

excel address function example1

#2 Absolute row, relative column, type formula: =ADDRESS(3,4,2)

excel address function example2

#3 Absolute row, relative column in R1C1 reference style, type formula:=ADDRESS(3,4,2,FALSE)

excel address function example3

More Excel ADDRESS  Function Examples