Index experts needed

[churmie]

Hi

I am trying to build a spreadsheet that will produce several results based on referencing one value in one particular cell.

How do I build a formula that will produce the results like in Match? Leaving blanks if the incorrect Animal I input in Lookup?

Any help will be HUGELY appreciated!

The example is below:

Lookup: Fox

Animal Name Colour Origin Owner
Match: Fox Dave Red US No
Fox Harry Red Italy No

	A	B	C	D	E
1	ANIMAL	NAME	COLOUR	ORIGIN	OWNER
2	Fox	Dave	Red	US	No
3	Rabbit	Steve	Grey	Italy	Yes
4	Rabbit	John	Black	Germany	Yes
5	Dog	Chris	White	UK	Yes
6	Cat	Brian	Ginger	UK	Yes
7	Fox	Harry	Red	Italy	No
8	Dog	Derek	Red	Denmark	Yes
9	Cat	Kane	Red	France	No

<tbody>
</tbody>

 

[Scott T]

Excel 2010
	A	B	C	D	E
1	ANIMAL	NAME	COLOUR	ORIGIN	OWNER
2	Fox	Dave	Red	US	No
3	Rabbit	Steve	Grey	Italy	Yes
4	Rabbit	John	Black	Germany	Yes
5	Dog	Chris	White	UK	Yes
6	Cat	Brian	Ginger	UK	Yes
7	Fox	Harry	Red	Italy	No
8	Dog	Derek	Red	Denmark	Yes
9	Cat	Kane	Red	France	No
10
11
12
13	Lookup	fox
14	ANIMAL	NAME	COLOUR	ORIGIN	OWNER
15	Fox	Dave	Red	US	No
16	Fox	Harry	Red	Italy	No
17
18
19
20
Sheet5

Cell Formulas
Range	Formula
A15	{=IF(ROWS(A$15:A15)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(A$2:A$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A15))))}
A16	{=IF(ROWS(A$15:A16)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(A$2:A$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A16))))}
A17	{=IF(ROWS(A$15:A17)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(A$2:A$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A17))))}
A18	{=IF(ROWS(A$15:A18)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(A$2:A$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A18))))}
A19	{=IF(ROWS(A$15:A19)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(A$2:A$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A19))))}
A20	{=IF(ROWS(A$15:A20)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(A$2:A$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A20))))}
B15	{=IF(ROWS(B$15:B15)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(B$2:B$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$15:B15))))}
B16	{=IF(ROWS(B$15:B16)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(B$2:B$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$15:B16))))}
B17	{=IF(ROWS(B$15:B17)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(B$2:B$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$15:B17))))}
B18	{=IF(ROWS(B$15:B18)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(B$2:B$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$15:B18))))}
B19	{=IF(ROWS(B$15:B19)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(B$2:B$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$15:B19))))}
B20	{=IF(ROWS(B$15:B20)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(B$2:B$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$15:B20))))}
C15	{=IF(ROWS(C$15:C15)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(C$2:C$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(C$15:C15))))}
C16	{=IF(ROWS(C$15:C16)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(C$2:C$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(C$15:C16))))}
C17	{=IF(ROWS(C$15:C17)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(C$2:C$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(C$15:C17))))}
C18	{=IF(ROWS(C$15:C18)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(C$2:C$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(C$15:C18))))}
C19	{=IF(ROWS(C$15:C19)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(C$2:C$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(C$15:C19))))}
C20	{=IF(ROWS(C$15:C20)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(C$2:C$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(C$15:C20))))}
D15	{=IF(ROWS(D$15:D15)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(D$2:D$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(D$15:D15))))}
D16	{=IF(ROWS(D$15:D16)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(D$2:D$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(D$15:D16))))}
D17	{=IF(ROWS(D$15:D17)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(D$2:D$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(D$15:D17))))}
D18	{=IF(ROWS(D$15:D18)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(D$2:D$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(D$15:D18))))}
D19	{=IF(ROWS(D$15:D19)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(D$2:D$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(D$15:D19))))}
D20	{=IF(ROWS(D$15:D20)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(D$2:D$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(D$15:D20))))}
E15	{=IF(ROWS(E$15:E15)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(E$2:E$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$15:E15))))}
E16	{=IF(ROWS(E$15:E16)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(E$2:E$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$15:E16))))}
E17	{=IF(ROWS(E$15:E17)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(E$2:E$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$15:E17))))}
E18	{=IF(ROWS(E$15:E18)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(E$2:E$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$15:E18))))}
E19	{=IF(ROWS(E$15:E19)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(E$2:E$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$15:E19))))}
E20	{=IF(ROWS(E$15:E20)>COUNTIF($A$2:$A$9,$B$13),"",INDEX(E$2:E$9,SMALL(IF($A$2:$A$9=$B$13,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$15:E20))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[churmie]

Thanks Scott. I have put all the cells in the order you have, but all cells keep returning a blank? I remember of course to Ctrl+Shift+Return at the end, but it's just blank?

 

[churmie]

Ignore me! I think my workbook wasn't formatted correctly. Working just as you put it. Brilliant work, really appreciated Scott