Solved

# Index and Match optimization

Posted on 2014-02-26
350 Views
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))
0
Question by:CalmSoul
• 2

LVL 80

Expert Comment

ID: 39890888
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.
0

LVL 80

Accepted Solution

byundt earned 500 total points
ID: 39890893
Sample file with array-entered formula
0

## Featured Post

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
Read about how to approach blogging and about ways to do it right. Stand out from the crowd and let your knowledge be consumed by a large audience. This article aims to explain how your blog should look like,  the most important things to do while b…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.