index-match

[sna]

Hi there
i need your help to write a formula to populate in sheet Data column J and sheet output colulumn B,C

I attached a template

thanks

[puriwutpokin]

A5=LET(a,SEQUENCE(COUNTIF(Data!J11:J25,"?*")),HSTACK(a,INDEX(FILTER(Data!B11:J25,Data!J11:J25<>""),a,{9,1})))

OR
A5=LET(a,SEQUENCE(COUNTIF(Data!J11:J25,"?*")),HSTACK(a,CHOOSECOLS(FILTER(Data!B11:J25,Data!J11:J25<>""),9,1)))

[sna]

A5=LET(a,SEQUENCE(COUNTIF(Data!J11:J25,"?*")),HSTACK(a,INDEX(FILTER(Data!B11:J25,Data!J11:J25<>""),a,{9,1})))

OR
A5=LET(a,SEQUENCE(COUNTIF(Data!J11:J25,"?*")),HSTACK(a,CHOOSECOLS(FILTER(Data!B11:J25,Data!J11:J25<>""),9,1)))

Thanks one more how to populate column J in Sheet Data?

[puriwutpokin]

Sheet Data J11=XLOOKUP(B11,Output!C$5:C$16,Output!B$5:B$16,"") copy down

[sna]

Sheet Data J11=XLOOKUP(B11,Output!C$5:C$16,Output!B$5:B$16,"") copy down

No ,I mean create a helper column in column J of sheet "Data" first before retrieving results to sheet "Output"

Thanks

[norkaz]

...


J10
=LET(a,A10:A25,IF(ISNUMBER(a),LOOKUP(ROW(a),ROW(a)/(SEARCH("-",a)>0),a),""))



Bigcat9
Bangkok Thailand

[sna]

it works but if lower excel version without spill range?
=IF(B9="",A9,IF(A9<>"",J9))

[norkaz]

it works but if lower excel version without spill range?
=IF(B9="",A9,IF(A9<>"",J9))

...

Without spill array.

It would be better to skip text cells.

B10
=IF(ISNUMBER(A10),INDEX(LOOKUP(ROW($A$10:$A$26),ROW($A$10:$A$26)/(SEARCH("-",$A$10:$A$26)>0),$A$10:$A$26),ROWS($J$10:J10)),"")
Copy down below.



Bigcat9
Bangkok Thailand
...

[puriwutpokin]

it works but if lower excel version without spill range?
=IF(B9="",A9,IF(A9<>"",J9))

J11=IFERROR(IF(A11,LOOKUP(CHAR(255),A$10:A10),""),"") copy down

[sna]

it works but if lower excel version without spill range?
=IF(B9="",A9,IF(A9<>"",J9))

J11=IFERROR(IF(A11,LOOKUP(CHAR(255),A$10:A10),""),"") copy down

This nice thanks

[sna]

Thanks both expert