[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

Index and Match optimization

Following formula only find he first occurrence of value 10 and returns the column header in row 1 .. how can I make it to find all occurrence of value 10 in the row and output corresponding column header in row 1?

=INDEX($B$1:$G$1,MATCH(10,INDEX($B$2:$G$7,MATCH("Row 3",$A$2:$A$7,0),0),0))
  • 2
1 Solution
Here is an array-entered formula you might use:
=IFERROR(INDEX($B$1:$G$1,SMALL(IF(INDEX($B$2:$G$7,MATCH("Row 3",$A$2:$A$7,0),0)=10,COLUMN($B$2:$G$7)-COLUMN($B$2)+1,""),COLUMNS($B1:B1))),"")

To array-enter a formula:
1.  Select the cell, then click in the formula bar
2.  Hold the Control and Shift keys down
3.  Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula.

The formula will return an empty string (looks like  blank) if you don't array-enter it successfully. If so, repeat the steps.

The formula is designed to be copied across and return successive header labels until there are no more cells containing 10 in "Row 3". When the list is exhausted, the formula returns an empty string.
Sample file with array-entered formula

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now