## Match a criterion and extract multiple corresponding table headers

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

### Answer:

**Array formula in cell B7:**

### 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 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

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]Comments(16) Filed in category: Excel, Sort values

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]Comments(13) Filed in category: Excel, Sort values, Vlookup

### Category: Unique distinct values

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]Comments(79) Filed in category: Excel, Unique distinct values

Create a unique distinct alphabetically sorted list extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]Comments(53) Filed in category: Excel, Unique distinct values

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Extract a unique distinct list by matching items that meet a criterion in excel

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Comments(40) Filed in category: Excel, Unique distinct values

### 3 Responses to “Match a criterion and extract multiple corresponding table headers”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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