Extract table headers based on a condition

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 the formula returns A because 1 is found in cell C2 and C4.

The formula in cell C9 returns A because 2 is found once in cell range C3:C5, the condition is in column B.

The following array formula extrcats column headers based

Array formula in cell C8:

How to create an array formula

1. Copy array formula
2. Select cell B7
3. Paste formula in formula bar
4. Press and hold Ctrl + Shift
5. Press Enter

How to copy array formula

1. Select cell B7
2. Copy cell (not formula)
3. Select cell range C7:F7
4. Paste (Ctrl +v)

Explaining array formula in cell C8

Step 1 - Filter column numbers

The IF function checks if value in cell B8 is equal to any of the cells in cell range C3:G5.

IF($B8=$C$3:$G$5, COLUMN($C$3:$G$5), "")

IF(logical_test, [value_if_true], [value_if_false])

The logical_test is $B8=$C$3:$G$5, it returns TRUE if equal and FALSE if not equal.

IF($B8=$C$3:$G$5, COLUMN($C$3:$G$5), "")

becomes

IF("1"={1, 1, 2, 2, 2;2, 1, 1, 0, 0;1, 1, 1, 2, 0}, {1, 2, 3, 4, 5}, "")

becomes

IF({TRUE, TRUE, FALSE, FALSE, FALSE;FALSE, TRUE, TRUE, FALSE, FALSE;TRUE, TRUE, TRUE, FALSE, FALSE}, {1, 2, 3, 4, 5}, "")

and returns

{1, 2, "", "", "";"", 2, 3, "", "";1, 2, 3, "", ""}

This array tells us that 1 is found in column 1, 2 and 3.

Step 2 - Calculate the frequencies of the numbers in the array

The FREQUENCY function allows you to extract only one instance of each header.

FREQUENCY(IF($B8=$C$3:$G$5, COLUMN($C$3:$G$5), ""), COLUMN($C$3:$G$5))

becomes

FREQUENCY({1, 2, "", "", "";"", 2, 3, "", "";1, 2, 3, "", ""}, {1, 2, 3, 4, 5})

and returns

{2;3;2;0;0;0}

If a value in this array is larger than 0 (zero) you know that the corresponding header is found in the cell range.

Step 3 - Extract unique distinct numbers

The IF function returns the corresponding column number if a value is larger than 0 (zero).

IF(FREQUENCY(IF($B8=$C$3:$G$5, COLUMN($C$3:$G$5), ""), COLUMN($C$3:$G$5))>0, ROW($A$1:$A$5), "")

becomes

IF({2;3;2;0;0;0}>0, {1; 2; 3; 4; 5}, "")

and returns

{1; 2; 3; ""; ""}

Step 4 - Return the k-th smallest value

The SMALL function returns the k-th smallest value in the array. SMALL(array, k)

SMALL(IF(FREQUENCY(IF($B8=$C$3:$G$5, COLUMN($C$3:$G$5), ""), COLUMN($C$3:$G$5))>0, MATCH(ROW($C$2:$C$6), ROW($C$2:$C$6)), ""), COLUMN(A1))

becomes

SMALL({1; 2; 3; ""; ""}, 1)

and returns 1.

Step 5 - Return the value of a cell at the intersection of a particular row and column

The INDEX function returns a value based on a row and column number.

INDEX($A$1:$E$1, SMALL(IF(FREQUENCY(IF($B8=$C$3:$G$5, COLUMN($C$3:$G$5), ""), COLUMN($C$3:$G$5))>0, MATCH(ROW($C$2:$C$6), ROW($C$2:$C$6)), ""), COLUMN(A1)))

becomes

INDEX($A$1:$E$1, 1)

becomes

INDEX({"A", "B", "C", "D", "E"}, 1)

and returns A in cell B7.

Step 6 - Return value_if_error if expression is an error and the value of the expression itself otherwise

The IFERROR function returns a blank (nothing) if an errror is returned.

=IFERROR(INDEX($A$1:$E$1, SMALL(IF(FREQUENCY(IF($B8=$C$3:$G$5, COLUMN($C$3:$G$5), ""), COLUMN($C$3:$G$5))>0, MATCH(ROW($C$2:$C$6), ROW($C$2:$C$6)), ""), COLUMN(A1))), "")

becomes

=IFERROR("A", "")

and returns A in cell C8.

Get the Excel file

Match-a-criterion-and-extract-multiple-corresponding-table-headers.xlsx

Extract category

How to create name initials
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

How to extract numbers from a cell value
What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]

Extract numbers from a column
I this article I will show you how to get numerical values from a cell range manually and using an […]

Excel categories

Comments (4)