Excel tutorials

Return a List of all the Row Headers if Three Conditions are Meet

How to List all Row Headers if there Three Conditions, Values Between Two Numbers and Column Header

If you've ever had the need to Display Multiple Row Header in an Excel table if Three conditions were met and you did not know how, in this tutorial to show you how you can display a list of all employees who have earnings between the minimum and maximum values in a particular month of the year.
Take the situation as shown below. Notice the following data in the cell range.

  • In column "A" is a list of all workers.
  • In columns 'B' to 'M' are a list of all months of the year.
  • The range of cells 'B2: M24' are cash amounts paid workers

 If there three conditions list row headers between min and max values and column header

Our task is as follows:
For the three conditions set, we must list the list of all workers who meet the criteria set.

  • The first criterion is the minimum value (Excel cell 'P2')
  • The second criterion is the maximum value (Excel cell P3)
  • The third criterion is the name of the month of the year (Excel cell P4)

So, after entering all the cell ranges from 'P7' to 'P24' we want to show a list of all workers meeting the three criteria set.
In the image above, you notice the color of the cells I've duplicated (to help you understand this tutorial). In the B2: M24 cell range, I set Conditional Formatting to indicate the red color of the font.

The ARRAY formula (CSE formula) in 'P7' cell is the following: Copy this formula to Excel and place it in a single line in Formula Bar. Entering the formula ends with CSE.
Remark! This formula shows a list of last row results according to the first row that meets the set criteria.

=IF(ROWS($P$7:P7)>SUMPRODUCT(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4));"";
INDEX($A$2:$A$24;SUMPRODUCT(LARGE(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*
($B$1:$M$1=$P$4)*(ROW($B$2:$M$24)-ROW(B$2:M$2)+1);ROWS($P$7:P7)))))

To help you understand the formula, use Evaluate Formula, which you can see step by step through the formula calculation. The main formula is the Excel 'IF function' in which we have nested other functions like; SUMPRODUCT function, LARGE function, ROW function, ROWS function, INDEX function.

If we look at the syntax of Excel IF functions then we can observe the following:

=IF(logical_test;value_if_true;value_if_false)

To the first argument of the IF function using the following formula: (Rows > sumproduct)
ROWS($P$7:P7)>SUMPRODUCT(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4))

The first part of the formula in which we use the ROWS function returns the result number 1 as the row number (I said that the formula is in cell 'P7'). The second part is the SUMPRODUCT formula that returns the order number for certain conditions (Sumproduct multiplies three criteria), and the result is the number 4. So if we look at 1 is not bigger than 4 then the result for the first argument 'FALSE' (1> 4 = False).

If we copy this formula down then we have a situation (figure below). ROWS function copying down returns the regular line numbers.The SUMPRODUCT function continuously returns the result number '4'. If you are wondering what that 'number four' is, then you will notice that this formula has three conditions. Since Sumproduct formula found a column in which the first two conditions were met and counted that '4' cells contain values that meet the conditions in the column set as the third condition.

How much cells match three conditions in column

Since the first argument returns the result of 'FLASE', the Excel IF function will return the result from the third argument.
If the result of this first argument is 'TRUE', the final result of the formula will be empty cell (see picture above).

For the third argument, the following formula is set: (Index (range, order number))
INDEX($A$2:$A$24;SUMPRODUCT(LARGE(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4)*
(ROW($B$2:$M$24)-ROW(B$2:M$2)+1);ROWS($P$7:P7))))

In this formula, the base is the INDEX function that returns the data from the cell range based on the number of rows and columns (see the syntax of the function on the link above). So this formula in the second argument will return the number '21'. That number '21' is the result of the Sumproduct formula. That we understand immediately. We could omit the Sumproduct function and only use LARGE function, the result would be the same. This is the order number in which our first data is found if we are based on the range beginning in the second line (hence the actual number of rows 22-1 header=21). Notice in the picture above that it is the last line in which our three conditions are met (see 'L22' cell in the picture above).

Copying this CSE formula automatically changes the row numbers that meet three set conditions so that the formula returns the row header from the 'A' column.

If we look at the third argument of the 'IF function' then we can see (see picture below) that the combination of the Sumproduct / Large function or the Large function itself returns the same result and that is the order number of the value that satisfies all three conditions (see ' Y and Z 'columns in the picture below). When LARGE function ie. Formula nest within the INDEX function then the result will be shown in the respective row or first column of the specified range of cells. Thus, in the 'AA' column of the image below, see the results of the 'A' column of cell ranges that we set ('($A$2:$A$24'), so the formula returns the contents of all cells from the 'A' column for that row.

Furthermore, also note in the 'AA' column of the image that the formula has returned the first data from the 'A' column to the end and that is the name 'ddd'. Of course, this does not suit us so we will solve this with the "first and second" argument of the 'IF function'. Note the cells marked with a gray paint background. For each formula result in which the first argument 'IF function = TRUE', the third argument ("ddd") will not be displayed because the 'IF function' will return the result as empty cell.

In the picture below, see what the "real rows" are labeled with numbers 1 to 24, which are "cell range rows" with 'order number 1' in cell 'L2'.

Results step by step of sumproduct large functions

How well did I clarify and how much did you understand? I do not know, but the images speak more than 1000 words.

So the final formula looks like this:

=IF(ROWS($P$7:P7)>SUMPRODUCT(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4));"";
INDEX($A$2:$A$24;LARGE(($B$2:$M$24>$P$2)*($B$2:$M$24<$P$3)*($B$1:$M$1=$P$4)*(ROW($B$2:$M$24)-ROW(B$2:M$2)+1);ROWS($P$7:P7))))

How to display the list of data from the column in reverse order

Use the following formula below if you want to invert the reverse order of the displayed list. Place this formula for example in 'S7' cell and copy it down.

=INDEX($P$7:$P$24;LARGE(IF($P$7:$P$24<>"";ROW($P$7:$P$24)-MIN(ROW($P$7:$P$24))+1;"");ROWS($S$6:S6)))

Reverse list data from excel column

And finally, here's another example in the picture below, how to display a list of all row headers based on three conditions (the condition between two values and the name of the month in the year)

Display list multiple rows header based on 3 conditions

 

Please note when using the SUMPRODUCT Excel function. If you have lots of rows and data or if you have a lot of formulas that contain the Sumproduct function, then it can slow you down when calculating Excel.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.