multiple results from index match table, please help

[daveexcellearner]

Hi All, I am chasing some assistance on this table i have been working on all weekend.
I am new to the forum, thanks

In the below table i want to lookup a date out of range E4:I9 from G11 and return the row 3 heading, using index match ect.
the formulas used and dragged down from f12 and g12 are below.

I can get the desired results for column F (which farms are on the specified date) even with multiple results, works great, but i cannot get column G to work if there is more than one date, G12 is the problem formula.

Thanks in advance

1	C	D	E	F	G	H	I
2			FARM PLANTING	GUIDE
3			APPLES	PEARS	GRAPES	ORANGES	BEANS
4		FARM 1	07-06	07-06	04-08	06-01	02-08
5		FARM 2	01-08	05-09	07-06	06-02	23-02
6		FARM 3	07-06	01-04	03-03	07-06	27-02
7		FARM 4	04-08	05-06	07-06	05-09	31-10
8		FARM 5	09-06	07-06	05-11	08-12	04-08
9		FARM 6	02-09	08-06	07-06	08-09	25-12
10
11			LOOKUP DATE	07-06	desired result
12				FARM 1	APPLES	apples
13				FARM 1	APPLES	pears
14				FARM 2	GRAPES	grapes
15				FARM 3	APPLES	apples
16				FARM 3	APPLES	oranges

<tbody>
</tbody>

Formula in F12 is
=IFERROR(INDEX($D$4:$D$9,SMALL(IF($G$11=$E$4:$I$9,ROW($E$4:$I$9)-ROW($E$4)+1),ROWS(F$12:F12))),"NIL")

Formula in G12 is
=IFERROR(INDEX($E$3:$I$3,MATCH($G$11,INDEX($E$4:$I$9,MATCH($F12,$D$4:$D$9,0),),0)),"NIL")

 

[Aladin Akyurek]

Row\Col	D​	E​	F​	G​	H​	I​
1​	​	FARM PLANTING​	GUIDE​	​	​	​
2​	​	APPLES​	PEARS​	GRAPES​	ORANGES​	BEANS​
3​	FARM 1​	6/7/2016​	6/7/2016​	8/4/2016​	1/6/2016​	8/2/2016​
4​	FARM 2​	8/1/2016​	9/5/2016​	6/7/2016​	2/6/2016​	2/23/2016​
5​	FARM 3​	6/7/2016​	4/1/2016​	3/3/2016​	6/7/2016​	2/27/2016​
6​	FARM 4​	8/4/2016​	6/5/2016​	6/7/2016​	9/5/2016​	10/31/2016​
7​	FARM 5​	6/9/2016​	6/7/2016​	11/5/2016​	12/8/2016​	8/4/2016​
8​	FARM 6​	9/2/2016​	6/8/2016​	6/7/2016​	9/8/2016​	12/25/2016​
9​	​	​	​	​	​	​
10​	​	LOOKUP DATE	6/7/2016​	8​	​	​
11​	​	​	FARM 1​	APPLES​		​
12​	​	​	FARM 1​	PEARS​		​
13​	​	​	FARM 2​	GRAPES​		​
14​	​	​	FARM 3​	APPLES​		​
15​	​	​	FARM 3​	ORANGES​		​
16​	​	​	FARM 4​	GRAPES​	​	​
17​			FARM 5​	PEARS​
18​			FARM 6​	GRAPES​
19​			​

In G10 just enter:

=COUNTIFS(E3:I8,F10)<strike></strike>

In F11 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$11:F11)<=$G$10,INDEX($D$3:$D$8,SMALL(IF($E$3:$I$8=$F$10,
    ROW($D$3:$D$8)-ROW($D$3)+1),ROWS($F$11:F11))),"")<strike></strike>

In G11 control+shift+enter and copy down:

=IF($F11="","",INDEX($E$2:$I$2,
    SMALL(IF(INDEX($E$3:$I$8,MATCH($F11,$D$3:$D$8,0),0)=$F$10,
    COLUMN($E$2:$I$2)-COLUMN($E$2)+1),COUNTIFS($F$11:F11,F11))))<strike></strike>

 

[daveexcellearner]

Thanks, that works heaps better!!

 

[Aladin Akyurek]

Thanks, that works heaps better!!

You are welcome.