Index, Match - return column heading

[Decode]

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,

 

[Stormseed]

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]

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.

 

[Stormseed]

I am sorry but I did not understand what you need to accomplish. Please explain with a table on an excel spreadsheet.

Download Colo's HTML Maker to show your Excel worksheet as a post on this board:

http://puremis.net/excel/cgi-bin/cl...tp://puremis.net/excel/soft/HtmlMaker2.42.zip

 

[Aladin Akyurek]

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
	A	B	C	D	E	F	G	H	I
1		Animal	Habitat	Noise	Food	Number of Legs		Meow	kwak
2		Dog	Kennel	Woof	Bone	4		Noise
3		Cat	House	Meow	Fish	4
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]

wooooof.........

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

Please Aladin...if you can explain in brief I wud be obliged.

 

[Decode]

Thanks Aladin,

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

 

[Aladin Akyurek]

Thanks Aladin

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.