# Index, Match - return column heading

#### Decode

##### New Member
Hi,

Having trouble getting my head round how to solve the following. Have been trying various combinations of Match and Index, but am struggling to get my head around how the two formulas work together - each time I think i have it, it just returns N/A.

As a leading biological researcher I have the following headings in row A

Animal, Habitat, Noise, Food, Number of Legs

And appropriate values for many animals completed in the columns below -

Dog, Kennel, Woof, Bone,4.
Cat,House,Meow,Fish,4

If I had a value of Meow, could I use an Index, Match formula to return 'Noise'? Meow is a unique value, and won't appear in any other columns.

Any pointers much appreciated,

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Stormseed

##### Banned
If you have the value of "Meow" then you can return any of the column values for the same row. In other words, you can return any values including Cat, House, Fish or 4 - provided Meow is unique in your data or else the index(match()) function will return the first value it encounters while performing a lookup() on the given data.

#### Decode

##### New Member
Thanks for replying Stormseed, very kind of you.

I would like to return a value in the same column though - specifically just the column heading - rather than row. Meow is unique though.

##### MrExcel MVP
Hi,

Having trouble getting my head round how to solve the following. Have been trying various combinations of Match and Index, but am struggling to get my head around how the two formulas work together - each time I think i have it, it just returns N/A.

As a leading biological researcher I have the following headings in row A

Animal, Habitat, Noise, Food, Number of Legs

And appropriate values for many animals completed in the columns below -

Dog, Kennel, Woof, Bone,4.
Cat,House,Meow,Fish,4

If I had a value of Meow, could I use an Index, Match formula to return 'Noise'? Meow is a unique value, and won't appear in any other columns.

Any pointers much appreciated,
Book6
ABCDEFGHI
1 AnimalHabitatNoiseFoodNumber of LegsMeowkwak
2DogKennelWoofBone4Noise
3CatHouseMeowFish4
4
Sheet1

A1:

=""

A2 and downwards left empty.

H2:

Control+shift+enter...

=INDEX(\$A\$1:\$F\$1,MIN(IF(\$A\$2:\$F\$10=H\$1,COLUMN(\$A\$2:\$F\$10)-COLUMN(\$A\$2)+1)))

and copy across.

#### Stormseed

##### Banned
wooooof.........

that is a wild formula man !!!! I tried but cud not understand the nesting of Min() to Index().

#### Decode

##### New Member

Consider my mind blown. It works perfectly, but as Stormseed said - if you have time to offer a brief explanation, that would be great.

And thanks Stormseed, have downloaded HTML Maker and will use it going forwards.

Best

##### MrExcel MVP

You are welcome.

if you have time to offer a brief explanation, that would be great.
...

In

=INDEX(\$A\$1:\$F\$1,MIN(IF(\$A\$2:\$F\$10=H\$1,COLUMN(\$A\$2:\$F\$10)-COLUMN(\$A\$2)+1)))

For each cell in A2:F10 that is equal to H1, the column number of that cell is put in an array, that is, the evaluation of

COLUMN(\$A\$2:\$F\$10)-COLUMN(\$A\$2)+1

is an array that is something like:

{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,4,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

MIN applied to this array would return: 4, meaning the 4th column in A2:F10 has a cell equalling H1.

4 fed to INDEX retuns the 4th item from A1:F1.

Hope this helps.

Replies
13
Views
174
Replies
16
Views
885
Replies
2
Views
142
Replies
2
Views
83
Replies
5
Views
229

1,190,827
Messages
5,983,113
Members
439,824
Latest member
nellyc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back