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.
i need to extract the headers from a grid based on value in left most column
example
row header ---> a b c d e
data 1 1 2 2 2
2 1 1
1 1 1 2
so how to find out which all headers appear agst 1 or 2o 3 in each row
The following array formula extrcats column headers based
Array formula in cell C8:
How to create an array formula
- Copy array formula
- Select cell B7
- Paste formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
How to copy array formula
- Select cell B7
- Copy cell (not formula)
- Select cell range C7:F7
- 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.
Extract category
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
Functions in this article
More than 1300 Excel formulas
Excel categories
4 Responses to “Extract table headers based on a condition”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Is it possible with2 criteria? with intersection table lookup?
Thans in advanced
Azumi
Azumi,
can you explain in greater detail`?
Something Like this (Intersection Tables:
A B C
AA 1 1 2
BB 2 1 2
How to retrieve column headers with this criteria:
1 and AA --> result should be A and B
or
1 and 2 and AA --> result should be A, B and C
Thanks
Hi Oscar, It helped me a great deal.
I was wondering if is it possible to count also how many times 1 exists in each header? please see the link for better understanding my query. Thanks!