daveexcellearner
New Member
- Joined
- Apr 3, 2016
- Messages
- 2
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
<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")
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>
=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")