ID base on number

[sna]

Hi there ,
i need your help how to show ID base on number of values in column a.for example if column a listed 1 to 12 it has to show ID from the above of column a and paste it column c

thanks

[sna]

here is attached

[norkaz]

...

C2
=IF(ISTEXT(A2:A166),"",LOOKUP(ROW(A2:A166),ROW(A2:A166)/ISTEXT(A2:A166),A2:A166))


Bigcat9
RCA Bangkok Thailand

[sna]

Can it be done with excel 365 dynamic array?

[norkaz]

Can it be done with excel 365 dynamic array?

Absolutely yes!

Bigcat9
RCA Bangkok Thailand

[sna]

Possible solution with Lookup (char(255),A$10:A10)?

[norkaz]

Possible solution with Lookup (char(255),A$10:A10)?

...

It's possible to use the lookup like this.

C2

=IF(ISTEXT(A2),"",LOOKUP(CHAR(255),$A$2:A2))
Copy down

==============

For the Spill Array formula (Excel 365)

C2

=LET(y,A2:A166,IF(ISTEXT(y),"",SCAN(A1,y,LAMBDA(x,y,LOOKUP(CHAR(255),IF(ISNUMBER(y),x,y))))))


Actually, with the SCAN formula like that, the LOOKUP can be skipped in the formula.

C2

=LET(y,A2:A166,IF(ISTEXT(y),"",SCAN(A1,y,LAMBDA(x,y,IF(ISNUMBER(y),x,y)))))


Bigcat9 / Bangkok Thailand

...

[sna]

thank you so much