Vikas asks:

Answer:

Array formula in cell B7:

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 B7

Step 1 - Filter column numbers

IF($A7=$A$2:$E$4, COLUMN($A$2:$E$4), "")

becomes

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

and returns

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

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

FREQUENCY(IF($A7=$A$2:$E$4, COLUMN($A$2:$E$4), ""), COLUMN($A$2:$E$4))

becomes

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

and returns

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

 Step 3 - Extract unique distinct numbers

IF(FREQUENCY(IF($A7=$A$2:$E$4, COLUMN($A$2:$E$4), ""), COLUMN($A$2:$E$4))>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

SMALL(IF(FREQUENCY(IF($A7=$A$2:$E$4, COLUMN($A$2:$E$4), ""), COLUMN($A$2:$E$4))>0, ROW($A$1:$A$5), ""), 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

INDEX($A$1:$E$1, SMALL(IF(FREQUENCY(IF($A7=$A$2:$E$4, COLUMN($A$2:$E$4), ""), COLUMN($A$2:$E$4))>0, ROW($A$1:$A$5), ""), 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

=IFERROR(INDEX($A$1:$E$1, SMALL(IF(FREQUENCY(IF($A7=$A$2:$E$4, COLUMN($A$2:$E$4), ""), COLUMN($A$2:$E$4))>0, ROW($A$1:$A$5), ""), COLUMN(A1))), "")

becomes

=IFERROR("A", "")

and returns A in cell B7.

Download excel *.xlsx file

Match a criterion and extract multiple corresponding table headers.xlsm

Related posts:

Return multiple values if above frequency criterion in excel

Match two criteria and return multiple rows in excel

Extract cell values in a range using a criterion in excel

Extract a unique distinct list by matching items that meet a criterion in excel 2007 using pivot table

Search for multiple text strings in multiple cells in excel