Page 1 of 1
index-match
Posted: Tue Aug 06, 2024 3:24 pm
by 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
Re: index-match
Posted: Tue Aug 06, 2024 5:04 pm
by 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)))
Re: index-match
Posted: Tue Aug 06, 2024 9:22 pm
by sna
puriwutpokin wrote: Tue Aug 06, 2024 5:04 pm
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?
Re: index-match
Posted: Tue Aug 06, 2024 10:08 pm
by puriwutpokin
Sheet Data J11=XLOOKUP(B11,Output!C$5:C$16,Output!B$5:B$16,"") copy down
Re: index-match
Posted: Wed Aug 07, 2024 6:42 am
by sna
puriwutpokin wrote: Tue Aug 06, 2024 10:08 pm
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
Re: index-match
Posted: Wed Aug 07, 2024 7:43 am
by norkaz
...
J10
=LET(a,A10:A25,IF(ISNUMBER(a),LOOKUP(ROW(a),ROW(a)/(SEARCH("-",a)>0),a),""))
Bigcat9
Bangkok Thailand
Re: index-match
Posted: Wed Aug 07, 2024 8:24 am
by sna
it works but if lower excel version without spill range?
=IF(B9="",A9,IF(A9<>"",J9))
Re: index-match
Posted: Wed Aug 07, 2024 9:33 am
by norkaz
sna wrote: Wed Aug 07, 2024 8:24 am
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
...
Re: index-match
Posted: Wed Aug 07, 2024 9:38 am
by puriwutpokin
sna wrote: Wed Aug 07, 2024 8:24 am
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
Re: index-match
Posted: Wed Aug 07, 2024 10:09 am
by sna
puriwutpokin wrote: Wed Aug 07, 2024 9:38 am
sna wrote: Wed Aug 07, 2024 8:24 am
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
Re: index-match
Posted: Sun Aug 11, 2024 6:59 am
by sna
Thanks both expert