Blue Flower

Two Conditions - Multiple Row Headers and Column Header - Return a List of Multiple Intersections

If you need to display all the data if two conditions match the multiple header rows and a column header on the right place, On this site you will find an Excel tutorial where you can find formulas that will show a list of all results if two conditions match.

In the image below you can see duplicate data in the 'A' column (Row headers). In the first row, there are unique data (Column headers). Our goal is to display all intersection data for two conditions.

The conditions are in the following cells:

  • Condition in 'G2' cell -> Duplicate data in 'A' column (Multiple Row headers)
  • Condition in 'H2' cell -> Data is unique, in the first row (Column header)

So we want to return all the information that is in the intersection of these two conditions. Considering that we have multiple header rows, it is necessary to display all data that meets the two conditions (found by the Excel formula).

Multiple row headers and column headers - Return a list of multiple intersections

Show all the intersections if the row headers and the column header match.

To solve this problem, ie to display all the intersection data for the two conditions row/column, you need to find all the rows in which the first condition appears.

To find the number of rows that meet the first condition we can use the ARRAY formula below. If you are wondering why we use the Array (CSE) formula then notice that we have multiple rows (duplicates) in which our condition "House5" is located, and to separate row numbers in the formula we use the range of '$A$2:$A$20' and create array formula. Copy the formula below and see which row is the first condition.

=IF(ROWS($K$2:K2);SMALL(IF($A$2:$A$20=$G$2;ROW($A$2:$A$20)-ROW($A$2)+1);ROWS($K$2:K2));"")

To find the number of columns we will use the formula below. Here there is no need to use CSE formula because the data is unique, and not repeated. Thus, the formula will return only one number, denoting the number of columns in that cell range.

=MATCH(H$2;$B$1:$E$1;0)

After copying the formula, it is necessary to check if the formula returned the desired result. Our score should be a positive number. So, we want to turn value into number. This can be done using the Excel 'N' function and the IFERROR function which is a replacement for the IF/ISERROR function.

Finally, when the above formulas are neste in one final formula it may look like these two formulas below.

The ARRAY formula above the 'I2' cell is the following: (copy the formula below)

=IF(N(IFERROR(IF(ROWS($G$2:G2);SMALL(IF($A$2:$A$20=$G$2;ROW($A$2:$A$20)-ROW($A$2)+1);ROWS($G$2:G2));"")+0;""));INDEX($B$2:$E$20;IFERROR(IF(ROWS($G$2:G2);SMALL(IF($A$2:$A$20=$G$2;ROW($A$2:$A$20)-ROW($A$2)+1);ROWS ($G$2:G2));"")+0;"");MATCH(H$2;$B$1:$E$1;0));"")

The array formula above the 'K2' cell is the following: (copy the formula below)

=IF(N(IFERROR(IF(ROWS($K$2:K2);SMALL(IF($A$2:$A$20=$G$2;ROW($A$2:$A$20)-ROW($A$2)+1);ROWS($K$2:K2));"");""));INDEX($B$2:$E$20;IF(ROWS($K$2:K2);SMALL(IF($A$2:$A$20=$G$2;ROW($A$2:$A$20)-ROW($A$2)+1);ROWS($K$2:K2));"");MATCH(H$2;$B$1:$E$1;0));"")

How to Show All Data for Multiple Columns if Requirements for Duplicate Queues are met

If you want to return all data to multiple columns then use the formula below the image.



List of all the data for multiple terms of rows and columns

The ARRAY formula in 'H2' cell is the following: (copy across)
=IFERROR(INDEX($A$2:$E$20;SMALL(IF($A$2:$A$20=$G$2;ROW($A$2:$A$20)-1);ROWS($1:1));COLUMN(B$2));"")

or this ARRAY formula below

=IFERROR(INDEX($A$2:$E$20;SMALL(IF($A$2:$A$20=$G$2;ROW($A$2:$A$20)-1);ROWS($1:1));COLUMNS($F:G));"")

This part of the formula 'ROW($A$2:$A$20)-1' can be written like this 'ROW($A$1:$A$20)'

Notice the difference between these two formulas above. Excel functions are COLUMN and COLUMNS used. Both functions return the same result.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

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.