If error and Index function for dynamic table

[Gabrielle_erre]

Hi, I would like to know if any of you needed the index function for dynamic column... I have the following function and I can't figure out how to make it work with Tabelle2!F column wich is Tabelle2![Name] column dynamic... in F1 I have the name of the column.
Any reccomandations replacing Tabelle2!$F$2:Tabelle2!F156 with the dynamic column's name?

=IFERROR(INDEX(Tabelle2!$F$2:Tabelle2!F156, MATCH(0,INDEX(COUNTIF($D$1:D1,Tabelle2!$F$2:Tabelle2!F156),0),0)),"")


Thank's in advance!
Kindly,
Gabrielle

 

[Aladin Akyurek]

Care to post 10 rows from this table invluding the headers row?

 

[Gabrielle_erre]

Thank's foe your help Aladin...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2019-01-01
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2019-01-02[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2019-01-03[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2019-01-04
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2019-01-05[/TD]
[/TR]
</tbody>[/TABLE]
Current table where in D2 I apply the formula from bellow to show me the list values (data as standard). How can I make the red part for dynamic?
=IFERROR(INDEX(Tabelle2!$F$2:Tabelle2!F156, MATCH(0,INDEX(COUNTIF($D$1:D1,Tabelle2!$F$2:Tabelle2!F156),0),0)),"")



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date reference[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2019-01-01[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2019-01-03[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2019-01-04[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2019-01-01[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2019-01-05[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2019-01-02[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2019-01-03[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2019-01-04[/TD]
[/TR]
</tbody>[/TABLE]
Tabelle2

 

[Aladin Akyurek]

Making Tabelle2!$F$2:Tabelle2!F156 dynamic horizontally or vertically? The latter means the F range can become smaller and/or larger...

 

[Gabrielle_erre]

Horizontaly dynamic... If I will have rows until row F200 to not modify the formula but to make the formula dynamic.

 

[Aladin Akyurek]

Horizontaly dynamic... If I will have rows until row F200 to not modify the formula but to make the formula dynamic.

Just to make sure: Do we have Tabelle2!$F$2:F156 at one time and Tabelle2!$H$2:H156? This would mean "horizontal".

 

[Gabrielle_erre]

verticaly dynamic... my mistake.

 

[Aladin Akyurek]

verticaly dynamic... my mistake.

Define data in Formulas | Name Manager as referring to:

=Tabelle2!$F$2:INDEX(Tabelle2!$F:$F,MATCH(9.99999999999999E+307,Tabelle2!$F:$F))


Now you can...


In D1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(data,data),1))

D2: Date

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

=IF(ROWS($D$3:D3)>$D$1,"",MIN(IF(ISNA(MATCH(data,$D$2:D2,0)),data)))