excel small - Free Excel Tutorial

April 8, 2023April 8, 2023

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays.

1. Generate Array with ROW or COLUMN Functions

Array formulas often need to use “natural number” as parameters of the function, such as the second parameter of the LARGE function, OFFSET function in addition to the first parameter. Through the manual way to enter a constant array will be more trouble, and easy to make mistakes. Then we can use the ROW or COLUMN function in EXCEL to generate a sequence, this method is very convenient and fast.

The following formula produces a vertical array of natural numbers from 1 to 10.

{=ROW (1:10)}

The following formula produces a horizontal array of natural numbers from 1 to 10.

{=COLUMN(A:J)}

2. Generating two-dimensional Array from one-dimensional Array

Below we will show you how to construct a new two-dimensional array with two columns of data.

a. One-dimensional range rearrangement to generate two-dimensional array

If there is a list of students and the name column of that list contains 10 students’ names, we need to randomly place the students’ names in the name column into the cell range of 5 rows and 2 columns (a new two-dimensional array).

We can use the following steps to randomize the names of students in column B to a 5 row 2 column range, such as the cell range E1:F5.

STEP1# Select the cell range E1:F5

STEP2# Enter the following array formula in the formula bar

=INDEX(B2:B11,RIGHT(SMALL(RANDBETWEEN(A2:A11^0,999)/1%+A2:A11,ROW(1:5)*2-{1,0}),2))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

STEP4# You will see that the students’ names have been randomly placed in a two-dimensional range of array.

Let’s see how this array formula works.

=RANDBETWEEN(A2:A11^0,999)

The RANDBETWEEN function is used to generate an array of 10 values, where the elements are random integers between 1 and 1000. Since the elements are randomly generated, the size of the array elements is randomly ordered. The array formula generates an array of random integers as follows.

={484;203;468;525;702;220;13;163;386;54}

=RANDBETWEEN(A2:A11^0,999)/1%+A2:A11

The random integer array generated above is multiplied by 100 and then added to the ordinal array of 1 to 10. This ensures that the last two digits of the array elements are ordinal numbers 1 to 10.

={484;203;468;525;702;220;13;163;386;54}/1%+A2:A11

The above array formula returns the following array:

{48401;20302;46803;52504;70205;22006;1307;16308;38609;5410}

=ROW(1:5)*2-{1,0}

The ROW function generates a vertical array {1;2;3;4;5), and then subtracts a constant array {1,0} to produce a two-dimensional array of 5 rows and 2 columns.

=SMALL()

The result is taken as the second argument to the SMALL function, which sorts the array after multiplication and addition processing. Since the original size of the array is random, after sorting, the ordinal number corresponding to the last two digits of each element is randomly ordered.

=RIGHT()

The RIGHT function is used to extract the last two digits of each element, and the INDEX function is used to return the student’s name in the corresponding position in column B. In this way, the names in column B can be randomly populated into a two-dimensional array of 5 rows and 2 columns.

b. Combining two columns of data to create a two-dimensional array

We can use the VLOOKUP function to query from the right to the left, and we can use the array operation and IF function to swap two columns of data to generate a new two-dimensional array.

Here is an example of how the VLOOKUP function can be used to reverse the query by constructing a new array.

If you have a table of employee information, and you need to find the employee’s number by the employee’s name, you can use the VLOOKUP function in combination with the IF function to construct a two-dimensional array to find the corresponding employee number.

The employee information table is as follows:

To find the employee number by name, the steps are as follows.

STEP1# Select the cell B3

STEP2# Enter the following formula in the formula bar and press Enter

=VLOOKUP(B2,IF({1,0},E2:E11,D2:D11),2,)

STEP3# As you can see, Jerry’s employee number has been found.

Let’s see how the above formula works.

The core part of the formula is IF ({1,0},E2:E11,D2:D11), which uses a horizontal array {1,0} and two vertical arrays to perform operations to achieve the position of the column where the employee’s name and work number are swapped. Its returned memory arrays are：

{"Tom",1;"Jerry",2;"Jacey",3;"Wendy",4;"William",5;"Ocean",6;"scott",7;"charies",8;"ceila",9;"nicole",10}

The VLOOKUP function then queries the employee’s name in the two-dimensional array generated by the IF function and returns the corresponding employee number.

3. Extracting Sub-arrays From Data

In daily work, it is often necessary to extract part of the data from a column and reprocess it. For example, If you want to find out the list of employees who meet the specified requirements in the employee table.

The following describes how to extract some data from a column to form a subarray.

Suppose you have an employee salary table and you want to find out the names of employees whose salary is greater than $2000. The salary table is as follows.

You can refer to the following steps to obtain a list of employees who meet the requirements.

STEP1# First you need to select the cell range E2:E11

STEP2# Enter the following formula in the formula bar

=T(OFFSET(B1,SMALL(IF(C2:C11>2000,A2:A11),ROW(INDIRECT("1:"&COUNTIF(C2:C11,">2000")))),))

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Let’s see how the above formula works.

=IF(C2:C11>2000,A2:A11)

First use the IF function to determine whether the salary meets the conditions, if the salary is greater than $2000, then return the employee’s ID, otherwise return the logical value FALSE.

=ROW(INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″)))

The COUNTIF function is used to calculate the number of scores greater than 100 and is combined with the ROW function and INDIRECT function to generate a sequence of natural numbers from 1 to n.

=SMALL(IF(C2:C11>2000,A2:A11),ROW( INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″))))

Use the SMALL function to find the employee number whose salary is greater than $2000 and return the following memory array.

={1;3;5;10}

=OFFSET(B1,{1;3;5;10},)

The OFFSET function extracts the employee’s name from the result returned by the SMALL function and returns the following array of employee names.

={"Tom";"Jacey";"William";"nicole"}

=T(OFFSET(B1,{1;3;5;10},))

Finally, the T function is used to convert the multi-dimensional reference returned by the OFFSET function into a memory array.

4. Extracting Sub-array from a two-dimensional Array

The cell range A1:C10 contains data of text and numeric type, see the figure below.

If you want to extract all the text-based data from the specified cell range A1:C10, then you can use the following array formula.

=T(INDIRECT(TEXT(SMALL(IF(A1:C10>="",ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT("1:"&COUNTIF(A1:C10,"*")))), "r0c00"),))

Let’s see how the above formula works.

=IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10))

The IF function is used to determine the type of data in the cell range. If the cell value is the text, then let the cell’s line number multiplied by 100, and then add the cell’s column number, and then return a numeric result; if the cell value is not text type, then return the logical value FALSE.

={FALSE,102,FALSE;FALSE,202,FALSE;FALSE,302,FALSE;FALSE,402,FALSE;FALSE,502,FALSE;FALSE,602,FALSE;FALSE,702,FALSE;FALSE,802,FALSE;FALSE,902,FALSE;FALSE,1002,FALSE}

=ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”)))

The COUNTIF function is used to calculate the number of text values in the range of cells A1:C10, and combined with the ROW function and INDIRECT function to generate a series of natural numbers from 1 to n.

={1;2;3;4;5;6;7;8;9;10}

=SMALL(IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”))))

The SMALL function is used to extract the position information of the cell where the text is located and return a memory array.

={102;202;302;402;502;602;702;802;902;1002}

=INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),)

The TEXT function is used to convert the location information to R1C1 reference style, and then use the INDIRECT function to return to the cell reference.

=T(INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),))

Finally, the multi-dimensional references returned by the INDIRECT function are converted to memory arrays using the T function.

5. Fill the Merged Cells by Array Formula

In the merged cells, only the first cell has a value, while the rest of the cells are empty cells. When we work with the data, we may need to fill the empty cells in the merged cells with the corresponding values to meet the needs of the calculation.

The following is a product sales table, we need to fill the empty cells in the merged cells with the corresponding region name. The data table is as follows:

You can fill the data into the merged cells by using the following array formula.

=LOOKUP(ROW(A2:A13),ROW(A2:A13)/(A2:A13>""),A2:A13)

Let’s See How This Formula Works:

=ROW(A2:A13)/(A2:A13>””)

This formula assigns a non-empty cell in column A to the row number of that cell, and returns the error value #DIV/O! for empty cells, and finally returns a memory array.

{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;#DIV/0!;#DIV/0!;10;#DIV/0!;#DIV/0!;#DIV/0!}

Finally, the LOOKUP function is used to perform a fuzzy search and return the corresponding region name.

6. Convert Two-dimensional Array to one-dimensional Array

Some functions only support one-dimensional array as their arguments, not two-dimensional array. For example, the second argument of the MATCH function, the second argument of the LOOKUP function, and so on. If you want to complete the query in a two-dimensional array, you need to first convert the two-dimensional array to a one-dimensional array.

In the figure below, the cell range A1:C4 is a two-dimensional array, by using the following formula you can return the maximum value less than or equal to 100 in the cell range; LOOKUP function will perform a fuzzy search from a one-dimensional array returned by the SMALL formula, and return the value that matches the conditions.

The formula is as follows.

=LOOKUP(100,SMALL(A1:C4,ROW(1:12)))

Let’s see how this formula works:

=SMALL(A1:C4,ROW(1:12))

Because the cell range is 4 rows and 3 columns, it is a two-dimensional array containing 12 elements. You can generate a sequence of natural numbers from 1 to 12 by using the ROW function. Then use the SMALL function to sort the two-dimensional array and return a one-dimensional memory array. The result is as follows:

={16;60;78.6;79;97;97;99;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}

The LOOKUP function performs a fuzzy lookup by row and ignores the error value #NUM!. Finally, the maximum value less than or equal to 100 is returned, which is 99.

April 4, 2023April 4, 2023

Find Missing Numbers in a Sequence in Excel

If you’re working with data in Excel and notice that there are some missing numbers in a sequence, it can be frustrating to try and figure out where they are. Fortunately, there are a few ways to find these missing numbers quickly and easily.

This post will guide you how to find missing numbers in a sequence with a formula in Excel. How do I identify missing numbers in a consecutive series in Excel. How to find missing serial number in Excel 2013/2016.

This post will guide you through two methods to find missing numbers in a sequence in Excel: using a formula and using VBA code.

1. Find Missing Numbers in a Sequence in Excel

Assuming that you have a serial number list in Column B, and you want to find the missing number in this sequence list. How to achieve it.

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. Like this:

=SMALL(IF(ISNA(MATCH(ROW(B$1:B$20),B$1:B$20,0)),ROW(B$1:B$20)),ROW(B1))

Type this array formula into a blank cell, and then press Ctrl + Shift + Enter keys in your keyboard.

The missing numbers are listed in cells.

Note: this formula will check the given sequence from 1 to 20 if there are missing numbers. If so, returns the missing numbers.

Or you can use another array formula based on the Small function, the IF function, the Countif function and the row function to achieve the same result. Like this:

=SMALL(IF(COUNTIF($B$1:$B$10,ROW($1:$20))=0,ROW($1:$20),""),ROW(B1))

2. Find Missing Numbers in a Sequence using VBA Code in Excel

To find missing numbers in a sequence in Excel with VBA code, you can follow these steps:

Step1: Press Alt + F11 to open the Visual Basic Editor (VBE) or click Developer > Visual Basic.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBE window, right-click your workbook and click Insert > Module.

Find Missing Numbers in a Sequence in Excel vba 1.png

Step3: In the module window, paste the following code:

Find Missing Numbers in a Sequence in Excel vba 2.png

Sub FindMissingNumbers_ExcelHow()
    Dim rng As Range
    Dim outRng As Range
    Dim i As Long
    Dim n As Long
    Dim found As Range
    
    ' Prompt the user to select the range of data to export
    Set rng = Application.InputBox(prompt:="Select the range that contain a sequence list", Type:=8)
    Set outRng = Application.InputBox(prompt:="Select one cell as output range", Type:=8)
    
    n = Application.Min(rng) 'get the minimum number in the input range
    
    For i = 1 To Application.Max(rng) - n + 1 '
        Set found = rng.Find(n, LookIn:=xlValues, LookAt:=xlWhole)
        If found Is Nothing Then
            outRng.Value = n
            Set outRng = outRng.Offset(1)
        End If
        n = n + 1
    Next i
    
End Sub

Step4: Press ALT + F8 on your keyboard to open the Macro dialog box. Select the FindMissingNumbers_ExcelHow macro from the list and click the Run button.

Find Missing Numbers in a Sequence in Excel vba 3.png

Step5: Select one range of cells that contains a sequence to find missing numbers.

Find Missing Numbers in a Sequence in Excel vba 4.png

Step6: Select one cell as output range to place the missing numbers.

Find Missing Numbers in a Sequence in Excel vba 5.png

Step7: The missing numbers in the sequence will be listed in the output range.

Find Missing Numbers in a Sequence in Excel vba 6.png

3. Video: Find Missing Numbers in a Sequence in Excel

This video will demonstrate both a formula and VBA code to help you find missing numbers in a sequence in Excel.

4. Related Functions

Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

April 1, 2023April 1, 2023

How to Sum the Smallest N Values in Excel

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and SMALL functions. SMALL can return smallest values based on criteria, SUMPRODUCT can sum up these smallest values.

Through a simple instance, we will introduce you the syntax, argument and the usage of SUMPRODUCT and SMALL functions. We will let you know how the formula works step by step clearly. After reading the article, you may know that in which situations we can choose SUMPRODUCT function to sum data.

1. Sum the Smallest N Values Using SUM Formula

How to Sum the Smallest N Values in Excel 1

In the ‘Value’ column, there are 10 values from ‘1’ to ‘10’ in improper order. Now we want to sum the smallest 4 values from this list, how can we get the correct result?

Actually, we can sort these numbers from smallest to largest firstly by ‘Sort A-Z’ in excel.

Step1: Click ‘Data’ tab, under ‘Sort & Filter’ section click ‘Sort Smallest to Largest’ icon.

How to Sum the Smallest N Values in Excel 2

Step2: Then you can find numbers are ordered properly.

How to Sum the Smallest N Values in Excel 3

Step3: Then you can enter SUM function in B2 and select the first four numbers ->cell reference A2:A5 in the list.

Step4: Then you can get the sum of the smallest four values.

2. Sum the Smallest N Values Using SUMPRODUCT Formula

We can also get sum conveniently and correctly by just enter a formula. As we want to sum the smallest four numbers from range A2:A11, we can find the smallest N number or numbers by SMALL function actually, and then use SUMPRODUCT function to sum array directly.

Step1: In B2, enter the formula

=SUMPRODUCT(SMALL(A2:A11,{1,2,3,4}))

How to Sum the Smallest N Values in Excel 6

Step2: Press Enter after typing the formula.

How to Sum the Smallest N Values in Excel 7

We can see that this time we also get correct result 10. The formula works correctly.

HOW THIS FORMULA WORKS

The formula is converted as:

=SUMPRODUCT(SMALL({1;3;5;6;9;10;7;8;4;2},{1,2,3,4}))

Now we will show you how the formula works with the two functions.

For SMALL({1;3;5;6;9;10;7;8;4;2},{1,2,3,4}), refer to k value {1,2,3,4}, we find out the smallest 4 values in the array {1;3;5;6;9;10;7;8;4;2}.

Select SMALL({1;3;5;6;9;10;7;8;4;2},{1,2,3,4}) in the formula bar, press F9. We can get the result {1,2,3,4}.

How to Sum the Smallest N Values in Excel 10

Obviously, the final result is 1+2+3+4=10. Select SUMPRODUCT({1,2,3,4}) in the formula bar, press F9, 10 is displayed in formula bar.

How to Sum the Smallest N Values in Excel 11

3. Sum the Smallest N Values in Excel with User Defined Function with VBA Code

You can create a user-defined function in Excel to sum the smallest N values in a range of cells. Here are the steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: Click Insert > Module to insert a new module.

Step3: Copy and paste the following code into the module. Save the module and close the Visual Basic Editor.

How to Sum the Smallest N Values in Excel vba 1.png

Function SumSmallestN_Excelhow(rng As Range, n As Integer) As Double
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim temp As Double
    Dim sum As Double
    
    For i = 1 To n
        For j = i To rng.Cells.count - 1
            If rng.Cells(j).Value < rng.Cells(i).Value Then
                temp = rng.Cells(i).Value
                rng.Cells(i).Value = rng.Cells(j).Value
                rng.Cells(j).Value = temp
            End If
        Next j
        sum = sum + rng.Cells(i).Value
    Next i
    
    SumSmallestN_Excelhow = sum
End Function

Step4: enter the formula into a blank cell:

=SumSmallestN_Excelhow(A2:A11,4)

Where A2:A11 is the range of cells you want to sum and 4 is the number of smallest values you want to sum.

How to Sum the Smallest N Values in Excel vba 2.png

Step5: Press Enter to calculate the sum of the smallest 4 values in the range.

How to Sum the Smallest N Values in Excel vba 3.png

4. Video: Sum the Smallest N Values in Excel

This video will demonstrate how to sum the smallest N values in Excel using a formula or a User Defined Function with VBA code as the formula.

5. Related Functions

Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel INDIRECT function
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….
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

March 27, 2023March 27, 2023

How to get nth Match with One Criteria using INDEX/MATCH

This post will guide you how to get nth match based on one criteria with INDEX and MATCH in Excel. In the previous post, we used one excel formula to get the position of the nth occurrence of a value based on one criteria.

1. Get nth Match with One Criteria using INDEX/MATCH Formula

At this moment, we can also use it in the new formula to extract the nth match value using INDEX function. Like the below array formula:

{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

For example, 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))

Let’s see how this formula works

= SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2)

The detailed description for this formula, please continue reading: get the position of nth occurrence of a value in column

The SMALL function returns the position of the second occurrence of the string text “jenny” in the range B2:B10. If you want to get the third or nth match value in a range, just need to modify the nth in the formula as your need.

=INDEX(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))

The First INDEX function returns bonus value of the second match based on the position number returned by the SMALL function. So it returns $165.

2. Related Formulas

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…
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)…
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.…
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.…
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..

3. Related Functions

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 syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

October 14, 2022October 14, 2022

Excel Array

What is Excel Array?

In Excel functions and formulas, an array is a collection of data elements in one row, one column, or multiple rows and columns. Array elements can be numeric, text, date, logical and error values.

The dimension of the array is the direction of the rows and columns of the array. An array with one row and multiple columns is a horizontal array, and an array with one column and multiple rows is a vertical array. An array with multiple rows and columns has both vertical and horizontal dimensions.

The dimensionality of an array is the number of different dimensions in the array. An array with only one row or column is called a one-dimensional array; an array with two dimensions with multiple rows and columns is called a two-dimensional array.

The size of an array is expressed by the number of elements in each row and column of the array.

A one-dimensional horizontal array with 1 rows and N columns has a size of 1xN
A one-dimensional vertical array with 1 column and N rows has a size of Nx1
A two-dimensional array with M rows and N columns has a size of MxN

Excel Array Types

Constant Array

Constants arrays are string expressions that are written directly to the array elements in a formula and are identified by curly brackets “{}” at the beginning and end.

Constant arrays do not depend on the cell range, can be directly involved in the calculation of the formula.

Constant array elements can not be functions, formulas or cell references. Numeric constant elements can not contain dollar signs, commas and percent signs.

One-dimensional Array

The elements of a one-dimensional vertical array are separated by a colon “:“, the following is an array of numeric constants of size 5x1.

={10;20;30;40;50}

The elements of a one-dimensional horizontal array are separated by a comma “,”, and the following is an array of numeric constants of size 1×5:

={10,20,30,40,50}

Note: For text-based constant arrays, each element in the array is identified by quotation marks by default.

Two-dimensional Array

The elements of a two-dimensional array are separated by a semicolon “;” on each row and a comma “,” on each column.

The following is a 4×3 two-dimensional array of mixed data types containing numeric, text, date, logical, and error values.

10	20	30
A	B	c
#N/A!	#REF!	#NUM!
TRUE	FALSE	TRUE

={10,20,30;"A","B","c";"#N/A!",#REF!,#NUM!;TRUE,FALSE,TRUE}

The process of manually entering a constant array can be tedious, you can use cell references to simplify the input of constant groups, the steps are as follows:

STEP1# Enter the value of the array element in the cell area, such as A1:A4

STEP2# Enter the formula =A1:A4 in cell A5

STEP3# In the formula bar, select the above formula and press F9, the formula can be converted to a constant array

Array in Excel Range

Range array is actually a formula directly referenced in the cell range, the size of the array and the size of the constant is exactly the same. For example, the following formulas A1:A4 and B1:B4 are range arrays.

=SUMPRODUCT (A1:A4*B1:B4)

Array in Memory

A memory array is an array temporarily formed in memory by multiple values returned by a formula calculation. Memory arrays do not have to be stored in the cell range, and as a group can be directly nested in other formulas to continue to participate in the calculation. For example:

{ =SMALL(A1:A4,{1,2,3})}

In the above formula, {1,2,3} is a constant group, and the entire formula results in a memory array of 1 row and 3 columns consisting of the smallest 3 numbers in the range of cells A1:A4.

Here is the array in memory.

={10,20,30}

The difference between memory array and area array：

Range array is obtained by cell range reference, memory array is obtained by formula.
Range Array depends on the referenced cell range, the memory array exists independently in memory.

Name an array constant

A named array is a constant array, a range array, or a memory array defined using a named formula (i.e., a name) that can be called as an array in a formula.

Note: You cannot use constant arrays directly in custom formulas used for data validation and conditional formatting, but you can use named arrays created through the Name Manager.

February 3, 2022

Extract matching values From Two Lists

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, which is ok for the few values, but it would be a big deal to extract the matching values from the two lists having multiple cells, and doing it manually would be a foolish attempt because there are 90% chances that you would 100% get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting the matching values from the two lists into another separate list would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

extract matching values from two list1

General Formula

The Following Formula would help you compare and extract the matching values from the two lists into another separate list.

=FILTER(table1,COUNTIF(table2,table1))

This Formula is based on the FILTER and COUNTIF functions, where the table1(A2:A9) and table2 (B2:B8) are the named ranges, the list in the range (D2:D6) is the list containing the matching values by comparing both table1 and table2.

Syntax Explanations

Before going into the explanation of the Formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to executing the matching values from the two lists into another separate list:

Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
COUNTIF: It is a statistical function that counts the number of cells to meet specific criteria.
List: In this Formula, the list represents the two lists present in the excel worksheet to execute the common values.
Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.

Let’s See How This Formula Works

The FILTER function is used in this Formula to extract data based on a logical test created using the COUNTIF function:

=FILTER(table1,COUNTIF(table2,table1))

extract matching values from two list1

The COUNTIF function is being used to generate the actual filter.

=COUNTIF(table2,table1))

It’s worth noticing that we’re using table2 as the range argument and table1 as the criterion argument. In other words, we’re asking COUNTIF to count all values in table1 that occur in table2. We obtain an array with several results since we provide COUNTIF various values for criteria:

{1;2;0;1;0;0;0;1}

extract matching values from two list1

Note that the array has 8 counts for each element in the table1. A zero value denotes a value in a table1 that is not present in the table2. Any other positive number denotes a value in table1 that is also present in table2. As the include parameter, this array is passed straight to the FILTER function:

=FILTER(table1,{1;1;0;1;0;1;0;0;1;0;1;1})

extract matching values from two list1

The array is used as a filter by the filter function. Any item in the table1 connected with a zero would be eliminated, but any value related to a positive number would retain.

Extract Non-matching Values

If you want to remove the non-matching values from table1, values in table1 that do not present in table2, we would need to modify the above formula, which is stated as follows:

=FILTER(table1,NOT(COUNTIF(table2,table1)))

The NOT function reverses the COUNTIF result, and every non-zero integer returns FALSE, and any zero value returns TRUE. The output is a list of all the values in the table1 that aren’t on the table2.

extract matching values from two list1

Method two: Extract Matching Values Using INDEX

You can also create a newly formula to extract matching values without the FILTER function, but the Formula would become more complicated. And the formula based on the INDEX function. Like below:

=IFERROR(INDEX(table1,SMALL(IF(COUNTIF(table2,table1),ROW(table1)-ROW(INDEX(table1,1,1))+1),ROWS($D$2:D2))),"")

extract matching values from two list1

Except in Excel 365, this array formula must be typed using control + shift + enter.

The INDEX function, which takes table1 as an array parameter, lies at the heart of this Formula. The majority of the remaining Formula determines the row number for matching values. This expression creates a list of relative row numbers as follows:

=ROW(table1)-ROW(INDEX(table1,1,1)) +1

which yields a 12-number array reflecting the rows in table1:

{1;2;3;4;5;6;7;8;}

extract matching values from two list1

These are filtered using the IF function and the same methodology used above in the FILTER Function, but this time using the COUNTIF function:

=COUNTIF(table2,table1) / identify values that match

extract matching values from two list1

=IF(COUNTIF(table2,table1),ROW(table1)-ROW(INDEX(table1,1,1))+1)

The resultant array is as follows:

{1;2;FALSE;4;FALSE; FALSE;7;8; }

As the Formula is copied along the column, this array is sent immediately to the SMALL function.

The IFERROR function is designed to catch mistakes when a formula is copied down, and the matching values run out.

Related Functions

Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
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 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 IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
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 NOT function
The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…

February 2, 2022

Extract all Partial Matches

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it’s not a big deal; because you would prefer to manually extract the few partial matches into another separate range of cells without any need of the formula;

Then congratulations because you are thinking right, but let me add up that it would be a big deal to extract the multiple partial matches into another separate range of cells and doing it manually would be a foolish attempt because you would get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting multiple partial matches into another range of cells would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

General formula

The Following formula would help you out for extracting multiple partial matches into another separate range of cells:

=INDEX($B$1:$B$5,AGGREGATE(15,6,(ROW($B$1:$B$5)-ROW($B$1)+1)/ISNUMBER(SEARCH($D$1,$B$1:$B$5)),E2))

Syntax Explanations

Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to extracting multiple partial matches into a separate range or list of cells:

INDEX: In a range or array, this index function contributes to returning the value at a given position.
AGGREGATE: This function contributes to returning the aggregate result in a database or list of values in the excel sheet.
ROW: In Excel, this Row function contributes toreturning the row number as a reference.
Absolute Reference: The Absolute referenceis nothing but an actual fixed location in a worksheet.
ISNUMBER: This function contributes to returns TRUE when a cell contains a number and FALSE if there is no number.
SEARCH: This function contributes to locating the character between two text strings and returns to the number of the starting position of the first text string from the first character of the second text string.
Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
Minus Operator (-): This minus symbol contributes to subtracting any two values.
Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and to separate them from the rest of the elements.
Plus operator (+): This plus operator adds the values.
Division (/): This division symbol is used for dividing values or numbers.

Let’s See How This Formula Works:

The INDEX function is the primary function having AGGREGATE, which is highly useful to figure out the matches for each row in the extracted area:

=INDEX($B$1:$B$5,matched_values)

Almost all of the work is to determine and report which rows in “$B$1:$B$5” match the search string and report the position of each matching value to INDEX. This is accomplished by configuring the AGGREGATE function as follows:

=AGGREGATE(15,6,(ROW($B$1:$B$5)-ROW($B$1)+1)/ISNUMBER(SEARCH($D$1,$B$1:$B$5)),E2)

The very first input or argument, which is 15, instructs AGGREGATE to return the nth smallest values.

The second argument, 6, specifies whether or not to ignore mistakes.

The third argument is an expression that returns an array of results that match.

The fourth input, E2, it specifies the “nth” value.

AGGREGATE works on arrays, and for the third argument inside AGGREGATE, the expression below builds an array :

={(ROW($B$1:$B$5)-ROW($B$1)+1)/ISNUMBER(SEARCH($D$1,$B$1:$B$5))}}

In this case, the ROW function is used to build an array of relative row numbers, as that’s why SEARCH and ISNUMBER are combined to match the search string against values in the data, resulting in an array of TRUE and FALSE values.

TRUE behaves as 1 in this math operation, while FALSE behaves as 0. As a result, row numbers with a positive match are divided by 1 and survive the operation, whereas row numbers with non-matching values are destroyed and become #DIV/0 errors. AGGREGATE is configured to ignore errors; it ignores #DIV/0 errors and returns the “nth” smallest number from the remaining values.

Alternative Formula with SMALL Function

You can also use an alternative formula which is based on SMALL function and IF Function to achieve the same result:

=INDEX($B$1:$B$5,SMALL(IF(ISNUMBER(SEARCH($D$1, $B$1:$B$5)),ROW($B$1:$B$5)-ROW($B$1)+1),E2))

Note: this is an array formula, and to get the work done, please enter it with Control + Shift + Enter.

Related Functions

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 ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Excel SEARCH function
The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])…
Excel AGGREGATE function
The Excel AGGREGATE function returns an aggregate in a list or database and ignore errors or hidden rows. The syntax of the AGGREGATE function is as below:= AGGREGATE(function_num, options, ref1,[ref2])…

January 29, 2022January 29, 2022

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate column in Ms Excel easily, then congratulations, you are thinking right, but here a problem arises that there isn’t any doubt that by this way you can extract one or two matches into the separate column easily but with the aid of this way you cannot extract multiple matches into separate columns and if you would do that by this way then there are 90% chances that you would 100% get tired of it and can’t complete your task at the right time.

But don’t be worry about it because after carefully reading this article extracting multiple matches into the separate columns would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

General Formula:

For extracting multiple matches(items) into seprate columns you can use the Array Formula which is based upon INDEX and SMALL, which is stated as follows:

=IFERROR(INDEX(STU_Range,SMALL(IF(CLASS_Range=$E2, ROW(STU_Range) -MIN (ROW(STU_Range))+1),COLUMNS($E$2:E2))),"")

excel multiple matches into separate column1

Syntax Explanations:

Before knowing about how to use this formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to extracting multiple matches into the separate columns:

IFERROR: This Function returns a custom result whenever a formula generates an error and returns the expected result when no error is detected.
INDEX: In a range or array, this index function contributes to returning the value at a given position.
SMALL: From the given range of data, this small Function returns the N^th
IF: In Excel, this IF Function contributes to returning two different values, one value for the TRUE result and another for the FALSE result.
ROW: In Excel, this Row function contributes toreturning the row number as a reference.
MIN: From the range of input values, this MIN function contributes to returning the smallest numeric value.
Absolute Reference: The Absolute referenceis nothing but an actual fixed location in a worksheet.
COLUMNS: From a given reference, this Column function contributes to counting the columns.
Comma symbol (,): This symbol acts as a separator that contributes to separating a list of values.
Minus Operator (-): This minus symbol contributes to subtracting any two values.
Parenthesis (): The primary purpose of this parenthesis symbol is to group the various elements.
Name– It represents the input ranges in your worksheet.
Plus operator (+): This plus symbol adds the values.

Let’s See How This Formula Works:

To use this array formula for getting the work done, you must enter this formula with Control + Shift + Enter. As soon as you would enter this formula into the first cell, you need to drag it down and across to fill in the other cells.

As you can see in the above screenshot, this formula uses two names ranging: “ CLASS_Range ” and “ STU_Range,” where “ STU_Range ” refers to B2:B12 and on the other hand “ CLASS_Range ” refers to A2:A12.

You would definitely wonder that how this formula works to extract multiple matches into columns? So here is the answer. In this formula, we use the Small Function and INDEX function, which work together.

As the SMALL Function (dynamically constructed by IF) is used to obtain row number corresponding to an “nth match,” so after getting the row number from SMALL Function, this would then pass it into the INDEX function, which returns the value at that row, this is also the main motive of this formula.

The snippet “IF(CLASS_Range=$E2, ROW(STU_Range) -MIN (ROW(STU_Range))+1” tests the named range “ STU_Range ” for the value in E2. If the value is found, then from an array of relative row numbers, it would return a row number, which is created with:

=ROW(STU_Range) -MIN (ROW(STU_Range))+1

The output of this formula is :

{1;2;3;4;5;6;7;8;9;10;11}

excel multiple matches into separate column1

Now the final result is an array that would contain the numbers where there is a match, and FALSE where there is not any match found:

{1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE;FALSE;10;FALSE}

excel multiple matches into separate column1

Then this array goes into the SMALL Function. By expanding range(Given Below), The k value for SMALL (nth) returns:

COLUMNS($E$2:E2)

The SMALL function returns each matching row number, which is then supplied as the row_num to the INDEX function as the array with the range named “ STU_Range.”

excel multiple matches into separate column1

Notes:

Now, this question would pop up in your mind that how would it handle the errors? Then whenever the COLUMN would return a value for k that does not exist, the #NUM error would be thrown by the SMALL Function at the next moment. This usually occurs when all the matches have occurred. To tackle the errors, the formula is wrapped up in the Function named “IFERROR,” which would receive the errors and then return an empty string (” “).

Related Functions

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 IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
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])….
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 COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

September 3, 2020October 22, 2022

How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ features, and we also need the help of formula which is combined with excel functions. It sounds complex, but through this tutorial we will introduce you the way to create dynamical dropdown list clearly, we will show you the details by steps, you can follow each step to complete all operations and reach your goal finally.

Precondition:

Prepare a list full of different fruits. We may also add new fruits into the list. Now we want to create a dynamical dropdown list and sort fruits by alphabetically order.

How to Create Dynamical Drop-Down List 1

Method: Create Dynamical Drop-Down List and Sort Alphabetically

Step 1: Select this fruit list and click Formulas in ribbon, and select Define Name under Defined Names group.

How to Create Dynamical Drop-Down List 2

Step 2: In New Name dialog, enter Name as ‘List’, keep Scope unchanged, enter formula =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001)) into Refers to. Then click OK. Please notice that in this formula ‘Sheet1’ is current sheet name, $A$1 is the first cell of your selected data.

How to Create Dynamical Drop-Down List 3

Step 3: Create sheet2, in A1 enter the formula =IF(COUNTA(List)>=ROWS($A$1:A1), INDEX(List, MATCH(SMALL(COUNTIF(List, “<“&List), ROW(A1)), COUNTIF(List, “<“&List), 0)), “”). In this formula, ‘List’ is defined name in last step.

Step 4: As above formula is an array formula, so press Ctrl+Shift+Enter to get value.

We get the first fruit ‘Apple’ from original table.

Step 5: Drag the fill handle down till blank cell displays.

How to Create Dynamical Drop-Down List 6

Note:

If you are confused about above formula and cannot apply it correctly in your work with your instance, you can directly copy the whole list from sheet1 to sheet2, and then sort them by A->Z. Then when you adding new parameters into sheet1 original list, you have to copy this list into sheet2 and sort again.

How to Create Dynamical Drop-Down List 7

Step 6: Click Formulas in ribbon, and select Define Name under Defined Names group. Enter ‘SortList’ as Name, enter =Sheet2!$A$1:$A$20 into Refers to. Then click OK. Please notice that as we create a dynamically dropdown list, so we can add new parameters into the list without limit, in this instance we set ‘Refer to’ range $A$1:$A$20, so only the first 20 cells in A column can be listed in dropdown list. You can change the reference per your demand.

How to Create Dynamical Drop-Down List 8

Step 7: Select C2 to locate dropdown list. Click Data->Data Validation under Data Tools group to create data validation.

How to Create Dynamical Drop-Down List 9

Step 8: In Data Validation window, under Settings tab, under Allow dropdown list select ‘List’; Enter =SortList in Source. Then click OK.

How to Create Dynamical Drop-Down List 10

Step 9: Verify that dropdown list is created. Values are sorted by alphabetical order properly.

How to Create Dynamical Drop-Down List 11

Step 10: Add a new fruit ‘Longan’ into sheet1 original list. You can see that it is added into sheet2 list automatically with proper order, and it is also displayed in dropdown list properly.

Sheet1: Sheet2: Dropdown list:

How to Create Dynamical Drop-Down List 12

How to Create Dynamical Drop-Down List 13

How to Create Dynamical Drop-Down List 14

Related Functions

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 IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel COUNTA function
The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …

March 10, 2020March 8, 2020

How to Find the Smallest Value and Smallest Positive Value in Excel

Sometimes we want to find out the smallest positive value among a set of values. We should ignore the negative value and zero value when getting the smallest positive value. So we design different cases in this article to demonstrate how can we get the smallest positive value by different formulas.

Returns the Smallest Value by Small Function

If there are all positive values for a set of values, we can use SMALL function to get the smallest value directly. See the table below.

There are a set of values, obliviously the smallest value is 1. But if we want use a formula to find out the smallest value, we can use SMALL function.

Step 1: In D2 cell, enter the formula =SMALL(A2:B6,1).

Step 2: Click Enter to get the result.

Returns the k-th Smallest Value by Small Function

In above case, we enter the formula =SMALL(A2:B6,1) then we can get the smallest value 1. For SMALL function, the parameter is SMALL(array,k), k is ‘Returns the k-th minimum value in the dataset’. So, we can change k number to get the k-th smallest value.

Follow above steps, in D2 cell change the formula to =SMALL(A2:B6,2). Then we can get the second smallest value 2.

Returns the Smallest Positive Value by Small Function

Update above table a little. Replace a positive number with 0. So If we still use above SMALL function without any criteria, it will return 0 as the smallest value. So if we still want to use the SMALL function here, we need to count the number of zero value, then we need to add 1 base on the number of zero value.

Step 1: In D2 cell, enter the formula =SMALL(A2:B6,COUNTIF(A2:B6,0)+1), where COUNTIF function is used for counting the number of zero value.

Step 2: Let’s replace some values with zero values and check the formula again.

Returns the Smallest Positive Value by MIN Function

If we don’t want to add any criteria in SMALL formula to identify if zero value or negative value exists among the set of values, we can directly use MIN function.

See the table below, it contains both negative value and zero value.

Step 1: In D2 cell, enter the formula =MIN(IF(A2:B6>0,A2:B6)). IF function returns an array which satisfy the criteria ‘every value in the array >0’.

Step 2: Press control+shift+enter to returns value.

Related Functions

Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
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])….

December 10, 2018December 10, 2018

How to Get the First, 2nd or Nth Match Using Vlookup/Index/Match

This post will guide you how get the nth matching values with VLOOKUP function in Excel. How do I find the nth match value with Index/Match formula in Excel. How to find the first, second, third or nth matching value from a range of cells using VLOOKUP formula.

Find the First Match Value Using VLOOKUP
Find the First Match Value Using Index/Match
Find the Nth Match Value Using VLOOKUP
Find the Nth Match Value Using Index/ Small
Video: Find 2nd, 3rd or Nth Match Value

Find the First Match Value Using VLOOKUP

Assuming that you have a list of data in range A1:C6, and you want to find a text string “excel” in this table, and then return its sales value. How to find the first match value in Excel. You can use a formula based on the VLOOKUP function to achieve the result of finding the first matching value in sales column. Like this:

=VLOOKUP("excel",A1:C6,3,0)

Type this formula in a blank cell and then press Enter key in your keyboard. This formula should be returned the first matching value.

Find the First Match Value Using VLOOKUP1

Find the First Match Value Using Index/Match

You can also use another array formula based on the INDEX function in combination with the MATCH function to get the first match value. Just use the following formula:

=INDEX(C2:C6,MATCH(TRUE,EXACT("excel",A2:A6),0))

You need to type this formula into a blank cell and then press Ctrl +Alt +Enter keys to change it as array formula.

Find the First Match Value Using VLOOKUP2

Find the Nth Match Value Using VLOOKUP

If you want to find the second or third or nth matching value in range A1:C6 using VLOOKUP function. And you need to create a helper column before the sales column firstly. Then type the following formula into Cell C2:

=A2&COUNTIF($A$2:$A2,A2)

This formula will create unique lookup values in helper column.

Then you need to use the following formula based on the VLOOKUP function to find the second matching value:

=VLOOKUP("excel"&2,C1:D6,2,0)

Find the First Match Value Using VLOOKUP3

Note: if you want to find the third matching values, just replace “excel”&2 with “excel”&3. And if you need to find the nth matching values, just change number 3 to number n.

Find the Nth Match Value Using Index/Small

You can use another formula based on the INDEX function, The SMALL function, the IF function, and the ROW function to achieve the result of finding the Nth matching value. Just type the following array formula into a blank cell, and then press Ctrl +Alt + Enter keys.

=INDEX(C2:C6,SMALL(IF("excel"=A2:A6,ROW(A2:A6)-ROW(A2)+1),2))

Find the First Match Value Using VLOOKUP4

Video: Find 2nd, 3rd or Nth Match Value

Related Functions

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 SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
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 ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel EXACT function
The Excel EXACT function compares if two text strings are the same and returns TRUE if they are the same, Or, it will return FALSE.The syntax of the EXACT function is as below:= EXACT (text1,text2)…

October 29, 2018May 2, 2022

Ignoring Blank or Zero Cells with Conditional formatting

This post will guide you how to make conditional formatting ignore blank cells or zero cell in Excel. How do I force blank cells or zero cells to be ignored in conditional formatting in Excel. How to ignore blank cells or blank cells when applying conditional formatting in Excel.

Ignoring Blank with Conditional Formatting

When you create conditional formatting rules for a list of selected data, and you want to ignore all Blank cells, How to achieve it. Just do the following steps:

#1 select the source data that you want to apply the conditional Formatting.

#2 go to HOME tab, click Conditional Formatting command under Styles group. And select New Rule from the popup menu list. And the New Formatting Rule dialog will open.

#3 select Use a formula to determine which cells to format in the Select a Rule Type section.

#4 type the following formula into the Format values where this formula is true text box. Click Ok button.

=ISBLANK(B1)=TRUE

Note: the B1 is the first cell of the selected range of cells.

Ignoring zero with Conditional Formatting

If you want to ignore zero values with conditional Formatting, you can do the following steps:

#1 select the range of cells C2:C5

#2 go to HOME tab, click Conditional Formatting command under Styles group, and select New Rule, and the New Formatting Rule dialog will open.

#3 select Use a formula to determine which cells to format in the Select a Rule Type section.

#4 type the following formula into the Format values where this formula is true text box.

=AND(C2<>0,C2<=SMALL(IF(C$2:C$5<>0,$C$2:$C$5),2))

#5 click Format button, and switch to Fill tab in Format Cells dialog, select one color as the background color. Click Ok button.

#6 click Ok button. You will see that all zero values are ignored by conditional formatting.

Related Functions

Excel ISBLANK function
The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel AND function
The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…

October 10, 2018May 2, 2022

Find the Earliest and Latest Date in a Range of Dates in Excel

This post will guide you how to find the earliest date in a range of dates in Excel. How do I get the earliest and latest date in a range with a formula in Excel. How to find the highest and lowest date in a range of dates in Excel.

Assuming that you have a list of data in a range A1:C3 that contain dates, and you want to get the earliest date in these dates, how to achieve it. You can use a formula based on the MIN function or the SMALL function to get the earliest date. And if you want to get the latest date in those range of cells, you can use a formula based on the MAX function or the LARGE function.

Find Earliest (Lowest) Date

If you want to find the earliest date in the range of cells A1:C3, just type the following formula into a blank cell, and then press Enter key.

=MIN(A1:C3)

=SMALL(A1:C3,1)

Find Latest Date

If you want to get the latest date in a range of cells, you can use the following formula, and type it into a blank cell, and then press Enter key.

=MAX(A1:C3)

=LARGE(A1:C3,1)

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])….
Excel MAX function
The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
Excel LARGE function
The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …

September 14, 2018September 14, 2018

Find Closest Value or Nearest Value in a Range in Excel

This post will guide you how to find the closest value or nearest value in a range of cells or in a column in Excel. How do I find the closest match in a range to a given lookup value in Excel. How to find the closest larger and closest smaller values from a range of cells in excel.

Find Closest Value or Nearest Value in a Range

Assuming that you have a list of numbers in range of cells B1:B6, and you have a lookup value in Cell C1, and you want to find the nearest value that matches the lookup value in that range. How to achieve it.

You need to use an excel array formula based on the INDEX function, the MATCH function, the MIN function and the ABS function. Just like this:

=INDEX(B1:B6,MATCH(MIN(ABS(B1:B6-C1)),ABS(B1:B6-C1),0))

Type this formula into a blank cell D2, and press CTRL+SHIFT+Enter keys in your keyboard. and the nearest value is retrieved from the range B1:B6.

Find Closest Smaller Value

If you want to find the closest smaller value from a range of cells in excel, you need to use another formula based on the LARGE function and the COUNTIF function. Just like this:

=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,">"&C1)+1)

Type this formula into a blank cell, and then press Enter key. and the closest smallest value will be retrieved from the given range of cells.

Find Closest Largest Value

If you want to find the closest largest value from a range of cells in excel, you need to use another formula based on the SMALL function and the COUNTIF function. Just like this:

=SMALL($B$1:$B$6,COUNTIF($B$1:$B$6,”<”&C1)+1)

Type this formula into a blank cell, and then press Enter key. and the closest largest value will be retrieved from the given range of cells.

Related Functions

Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
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 syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
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])….
Excel LARGE function
The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
Excel ABS Function
The Excel ABS function returns the absolute value of a number.The ABS function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.The syntax of the ABS function is as below:=ABS (number)…

September 5, 2018

Copy and Paste Only Non-blank Cells

This post will guide you how to copy and paste only non-blank cells in Excel 2013/2016. How do I copy only non blank cells with VBA code in Excel. Or how to get non-blank cells from a range of cells with a formula in Excel.

This post will introduce four methods to copy and paste only non-blank cells in a selected range in Excel.

Method1: Copy non-blank cells with Filter feature

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. Just do the following steps:

#1 select the range of cells that you want to filter all non-blank cells.

#2 go to DATA tab, click Filter command under Sort & Filter group.

#3 the drop-down button should appear in the first cell of your range. Click on this drop down button. de-select the (Blanks) option and then click on the OK button.

#4 you will see that only non-blank cells are displayed in the selected range.

#5 then you can select all non-blank cells in the current range and press Ctrl + C shortcuts in your keyboard, and then press Ctrl + V keys to paste the selected cells into a destination cell.

Method2: Copy non-blank Cells with Go To Special Feature

You can also use the Go To Special feature to select all non-blank cells in the selected range of cells. Do the following steps:

#1 select the range of cells that you want to filter all non-blank cells.

#2 go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the popup menu list. The Go To Special window will open.

#3 select Constants radio button in the Select section under Go To Special dialog. Then click OK button.

#4 all non-blank cells in the selected range are highlighted.

#5 you can press Ctrl + C shortcuts to copy cells have been highlighted, and then press Ctrl + V keys to paste the selected cells into a destination cell.

Method3: Copy non-blank Cells with VBA Macro

You can also use an Excel array formula based on the LOOKUP function, the CHOOSE function, the INDEX function, the SMALL function, the IF function, the ROW function and the ROWS function to extract all non-blank cells from a cell. Like this:

=LOOKUP("xxxxx",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$9<>"",ROW($A$1:$A$9)),ROWS($B$1:B1)))))

Type this formula into cell B1, and then press Ctrl +Shift +Enter shortcuts to change this formula as array formula. And then drag the AutoFill Handle from Cell B1 to B9.

All non-blank cells from the range A1:A9 are extracted in the range B1:B9.

Method4: Copy non-blank Cells with Formula

You can also write an Excel VBA macro to copy and paste all non-blank cells, just do the following steps:

#1 select the range of cells that you want to filter all non-blank cells

#2 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#3 then the “Visual Basic Editor” window will appear.

#4 click “Insert” ->”Module” to create a new module.

#5 paste the below VBA code into the code window. Then clicking “Save” button.

Sub CopyPasteNonBlankCells()
    Application.Selection.SpecialCells(xlCellTypeConstants).Copy Destination:=Range("B1")
End Sub

#6 back to the current worksheet, then run the above excel macro. Click Run button.

#7 let’s see the result.

Related Functions

Excel LOOKUP function
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 syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel Choose Function
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 syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)…
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 ROWS function
The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

September 4, 2018September 4, 2018

VLOOKUP Return Multiple Values Horizontally

This post will guide you how to vlookup a value and then return multiple corresponding values horizontally in Excel. How do I lookup a value and return multiple matched values in the same row in Excel. Or how to return multiple values with VLOOKUP function in Excel.

VLOOKUP Return Multiple Values Horizontally

Assuming that you have a list of data that contain product name and product sales in the range A1:B6, and you want to vlookup a product “excel”” and return all sales value of the “excel” horizontally.

We have talked that the VLOOKUP function can be used to return only a corresponding value of the first occurrence of a lookup value. And if you want to return multiple values horizontally, how to achieve it.

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.

=INDEX($B$2:$B$6, SMALL(IF(A$2:$A$9="excel", ROW($A$2:$A$6)-ROW($A$2)+1), COLUMN(A1)))

Type this formula into a blank cell and then press Ctrl+Alt+Enter shortcuts to change this formula as array formula. And then drag the AutoFill handle from Cell C1 to E1.

You will see that the multiple values have been extracted into the same row.

Related Functions

Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
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 ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
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])….

December 11, 2017December 11, 2017

How to Find the nth Smallest Value

This post explains that how to get the nth smallest value in a single column, or single row or an array list in excel. How to extract relative value in the same row based on the position of the nth smallest value in a single column.

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. Let’s see the following formulas:

Get the First Smallest Value in the Range C2:C5

=SMALL(C2:C5,1)

This formula returns the smallest values in the range C2:C5.

Get the Second Smallest Value in the Range C2:C5

=SMALL(C2:C5,2)

The second argument of the SMALL function is set to 2, so it will extract the second smallest values in the range C2:C5.

Get the Third Smallest Value in the Range C2:C5

=SMALL(C2:C5,3)

To get the third smallest value in the range C2:C5, you can use the above formula.

Get the Nth Smallest Value in the Range C2:C5

So if you want to get the nth smallest value in one range, you just need to modify the second argument of the SMALL function to a numeric value that you need.

=SMALL(C2:C5, nth)

You can also use the SMALL function in combination with the INDEX function and MATCH function to extract the relative value in the same row position of the nth smallest in a range. For example, to get the name value in the same relative row position of the 2nd smallest salary value in the range C2:c5, you can write down the following excel formula:

=INDEX(B2:B5,MATCH(SMALL(C2:C5,2),C2:C5,0))

Let’s see how this formula works:

=SMALL(C2:C5,2)

This formula returns the second smallest value in the range C2:C5. It returns a numeric value.

=MATCH(SMALL(C2:C5,2),C2:C5,0)

The MATCH function returns the position number of the second smallest value in the range C2:C5. it returns 3.

=INDEX(B2:B5,MATCH(SMALL(C2:C5,2),C2:C5,0))

The INDEX function extract the value based on the position number that returned by the above MATCH function, so it returns one value in the range B2:B5 based on the returned position. it returns “jeffery”.

Related Formulas

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))…
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.…
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…
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…

Related Functions

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 syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …

December 10, 2017September 13, 2018

How to find nth Occurrence with Multiple Criteria Using INDEX/MATCH

This post explains that how to get the nth occurrence value with multiple criteria using INDEX and MATCH in excel. How to find the first, second or nth match with multiple criteria in excel. How to return the nth match on a multiple criteria using INDEX and MATCH.

In the previous post, we talked that get the nth match value with only one criteria.

Also, we also talked that how to Lookup the Value with Multiple Criteria to find the first occurrence match in excel.

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=Criteria1,IF(Range2=Criteria2,IF(Range3=Criteria3,IF(Range4=Criteria4,IF(Range5=Criteria5,ROW(Array)-ROW(INDEX(Array,1))+1))))),nth))

For example, if you want to get the bonus value in the excel range D2:D10 that must meet two criteria, and the first one is that the member name is equal to the string “jenny” in the range A2:A10, and the second criteria is that the location is equal to “London” in the range B2:B10. So we can write down the following array formula:

=INDEX(D2:D10, SMALL(IF(A2:A10="jenny",IF(B2:B10="London", ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1)),2))

Note: after entering this formula in the formula bar, you need to press CTRL+SHIFT+Enter key to convert it as array formula.

Let’s see how this formula works:

=ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1

This formula returns the relative position of the range A2:A10, it returns an array result like this:

{1;2;3;4;5;6;7;8;9}

For More detailed description, you can continue to read this post: How to Find the Relative Position in a Range or Table

=IF(A2:A10=”jenny”,IF(B2:B10=”London”, ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1))

This function is a nested IF function, it will check each item of range A2:A10 firstly and if it is equal to the string “jenny”, if TRUE, then continue to check each item of range B2:B10 if it is equal to the string “London”, if TRUE, return the relative position of row. Otherwise, return FALSE. So this formula returns an array result like this:

{FALSE;2;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE}

=SMALL(IF(A2:A10=”jenny”,IF(B2:B10=”London”, ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1)),2)

The SMALL function returns the second smallest value from the array result returned by the above nested IF function. It returns 5.

So far, we got the position of the second occurrence of bonus value that meet two criteria as requested above.

=INDEX(D2:D10, SMALL(IF(A2:A10=”jenny”,IF(B2:B10=”London”, ROW(A2:A10)-ROW(INDEX(A2:A10,1,1))+1)),2))

The INDEX function extract the values at a position that returned by the SMALL function. So it is the bonus value of the second occurrence in the range D2:D10. It is 165.

Related Formulas

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))…
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)…
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.…
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.…
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..

Related Functions

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 syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
Excel nested IF function
The nested IF function is formed by multiple if statements within one Excel if function. This excel nested if statement makes it possible for a single formula to take multiple actions.The syntax of Nested IF function is as below:=IF(Condition_1,Value_if_True_1,IF(Condition_2,Value_if_True_2,Value_if_False_2))….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

December 5, 2017December 8, 2017

How to Extract multiple match Values into different Columns or Rows

This post explains that how to extract multiple match values from a range, then put the values into the different columns in excel. And how to extract multiple matches into the different rows.

In the previous post, we talked that how to get the position of the nth occurrence of a value in a column, and this pose will refer to it to extract all matched values firstly, then put all returned values into the different columns.

Extract multiple match values into separate columns

If you want to fetch all matches from a range then put it into cells in different columns, you can use a combination with the INDEX function, the SMALL function, the IF function, the ROW function and the COLUMNS function to create a new excel formula.

For example, if you want to get all member names belong to “excel” team in the range B3:D7, then separate it into the different columns, such as: E2, F2…you can use the following formula:

=IFERROR(INDEX($D$3:$D$7,SMALL(IF($C$3:$C$7="excel",ROW($C$3:$C$7)-ROW(INDEX($C$3:$C$7,1,1))+1),COLUMNS($E$2:E2))),"")

Let’s see how the above formula works:

= ROW($C$3:$C$7)-ROW(INDEX($C$3:$C$7,1,1))+1

The ROW function returns the relative position of all rows in the range C3:C7. The returned result is another array.

= IF($C$3:$C$7=”excel”,ROW($C$3:$C$7)-ROW(INDEX($C$3:$C$7,1,1))+1)

The IF function will check if each value in range C3:C7 is equal to “excel”, if TRUE, returns its relative position, otherwise, returns FALSE. As this formula is an array formula, so it must be returned an array result.

=COLUMNS($E$2:E2)

The COLUMNS function returns a numeric values, it will be used by the SMALL function as its nth argument to indicate the position of the number that you want to return.

=SMALL(IF($C$3:$C$7=”excel”,ROW($C$3:$C$7)-ROW(INDEX($C$3:$C$7,1,1))+1),COLUMNS($E$2:E2))

The SMALL function returns the position of the nth smallest values from the numbers in an array result returned by the above IF function. The nth argument is specified by the result returned by the COLUMNS function.

=INDEX($D$3:$D$7,SMALL(IF($C$3:$C$7=”excel”,ROW($C$3:$C$7)-ROW(INDEX($C$3:$C$7,1,1))+1),COLUMNS($E$2:E2)))

The INDEX function returns a value at a position returned by the above SMALL function in the range D3:D7, so it should be the first matches of the member name that belong to the excel team in the range B3:D7.

And if you want to get the second matches into Cell F2, you just need to drag the fill hander in the Cell E2 to F2, then the second matches of the member name will be fetched in the Cell F2.

=IFERROR()

The IFERROR function will check if the result returned by the SMALL function is a #NUM error, then returns an empty string.

Extract multiple match values into separate rows

If you want to extract multiple matches into the separate rows in excel, just need to use ROWS instead of the COLUMNS function in the above formula as follows:

=IFERROR(INDEX($D$3:$D$7,SMALL(IF($C$3:$C$7="excel",ROW($C$3:$C$7)-ROW(INDEX($C$3:$C$7,1,1))+1), ROWS($E$2:E2))),"")

If you want to extract the second matches, then put the result into the cell E3, you just need to drag the Fill Handler in Cell E2 to E3. The second matches of the member name should be fetched in the Cell E3.

Related Formulas

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.

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 SMALL function
The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
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 ROW function
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 syntax of the ROW function is as below:= ROW ([reference])….
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])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel ROWS function
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 syntax of the ROWS function is as below:= ROWS(array)….
Excel COLUMNS function
The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….

December 3, 2017September 14, 2018

Excel SMALL Function

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

Description

The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array. It also can be used to get the nth smallest value from a range of cells or an array. For example, the SMALL function can find the second smallest value, or third smallest value in a list or an array.

The SMALL function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.

The SMALL 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 SMALL function is as below:

= SMALL(array,nth)

Where the SMALL function arguments are:

array -This is a required argument. An Array or a range of cells that contains numeric values from which you want to get the nth smallest value.
nth – This is a required argument. The position of the number that you want to return

Note:

If the first argument is empty, then the small function returns #NUM! Error.
If nth is less than or equal to 0 or if nth is greater than the number of values in array, the SMALL function returns the #NUM! Error.
If nth is the number of values in array, then SMALL(array,1) returns the smallest value, and SMALL(array,n) returns the largest value.

Excel SMALL Function Examples

The below examples will show you how to use Excel SMALL Function to get the smallest value form the numbers in a range.

#1 To get the smallest value from a range B1:B4, just using the following excel formula:

 =SMALL(B1:B4,1)

The above formula returns the smallest number from a range B1:B4, it returns 2.

2# To get the second smallest value from the Range B1:B4, you can use the following SMALL function:

=SMALL(B1:B4,2)

The second argument nth in the above SMALL function is 2, so it will fetch the second smallest function in the range B1:B4. It returns 23.

3# To get the smallest value from the range B1:B4 that contains blank cells, enter into the following formula in the Cell C1:

=SMALL(B1:B4,1)

The SMALL function will ignore blank cells in the Range. You will see that the Cell B3 is a blank cell, and it is ignored by the small function. The smallest value is still returned as 23 by the SMALL function.

4# To get the smallest value from the range B1:B5 that contains text string, specific character and logical values. Using the following SMALL formula:

=SMALL(B1:B5,1)

The SMALL function will ignore values in the range B1:B5 that contains text string, specific character and logical value.

More Excel Small 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.…
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..…
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…