Formula to Repeat a TEXT or Number in a data

[odartey]

Hello All,
Trust you are all doing great.
Very grateful for the help i received for the Matrix table.
I am now working on a data set of which i require a function which will help me repeat the relevant NUMBER as and when i drag the formula down.
With the data i have attached, the unique numbers are 101, 102, 105, must be repeated in column i have highlighted in Yellow.
Please find attached a sample data.
Thanking you in advance for your immeasurable help.

 

[eibi]

Array formula:

=IF(OR(ISBLANK(I16),I16="Period Code"),"",INDEX($E$1:E16,MAX(IF($E$1:E16="AC_NO",ROW($E$1:E16)-2))))

See attached.

I hope somebody else can do this without an array...

 

[bosco_yip]

Another option,

In J7, formula copy down :

=IF(N(G7),INDEX(E:E,MATCH("zzzz",E$1:E6)-2),"")

Or,

=IF(N(G7),INDEX(E:E,MATCH("*",E$1:E6,-1)-2),"")

Regards
Bosco

 

[eibi]

@bosco_yip

Your recent comments on other posts have really been a good challenge for me to avoid unnecessary array formulas.

(appreciate the N(G7) too...)

Mine isn't as short as yours, but I did come up with one:

=IF(N(G7),LOOKUP(2,1/($E$3:E7="AC_NO"),$E$1:E7),"")

 

[bosco_yip]

Another one similar to eibi's idea…....

=IF(N(G7),LOOKUP(2,1/ISTEXT($E$3:E7),$E$1:E7),"")

Regards
Bosco

 

[odartey]

Array formula:

=IF(OR(ISBLANK(I16),I16="Period Code"),"",INDEX($E$1:E16,MAX(IF($E$1:E16="AC_NO",ROW($E$1:E16)-2))))

See attached.

I hope somebody else can do this without an array...

Awww......... I am sooooo happy and grateful to you guys.
Thanks eibi and bosco_yip