Forum Discussion
xlookup to combine multiple values in single cell
So it took me a minute to decipher your formula, as I'm still quite new to excel functions, but I think I've figured out a way to make it work. I had some trouble with the LET function, specifically in regards to "LL", as LEN(C1) returned 0 (at least in my workbook), which affected the rest of the calculation. I ended up googling "how to split a cell into an array" which resulted in this:
=1*(TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),IF(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)=1,1,(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)-1)*99),99)))I then adapted it to fit my data and replaced your LET function with a slightly more simplified one. It's not quite as adaptable as yours, but it does the trick:
=IF(ISBLANK([@Date]),0, IF(ISNUMBER(SEARCH("hourly",[@[Fee Type]])), "hourly", SUM(INDEX( IFS([@Date]>=Pricing!D$6, Pricing!D$6:D$15, [@Date]>=Pricing!E$6, Pricing!E$6:E$15), LET(inArray,TRIM(MID(SUBSTITUTE([@Service],",",REPT(" ",99)),IF(SEQUENCE(LEN([@Service])-LEN(SUBSTITUTE([@Service],",",""))+1)=1,1,(SEQUENCE(LEN([@Service])-LEN(SUBSTITUTE([@Service],",",""))+1)-1)*99),99)), IFERROR( XMATCH(inArray,Pricing!B$6:B$15), XMATCH(inArray,Pricing!C$6:C$15)) ))) ) )I've also linked a workbook demo which I've removed most of the data from for reference. (please excuse the clunky formulas in other columns--I have yet to update them haha)
Chloe_52 You're right that attaching a sample sheet would help a lot. That said I can see an improvement to your equation. I believe this would be equivalent to the existing equation:
=IF(ISBLANK([@Date]),0,
IF(ISNUMBER(SEARCH("hourly",[@[Fee Type]])),
"hourly",
INDEX(
IFS([@Date]>=Pricing!D$6, Pricing!D$6:D$15,
[@Date]>=Pricing!E$6, Pricing!E$6:E$15,
TRUE, IF(ROW(Pricing!B$6:B$15),"Date Error")),
IFERROR(
XMATCH(D13,Pricing!B$6:B$15),
XMATCH(D13,Pricing!C$6:C$15))
)
)
)
now to add that extra function gets tricky but there are new functions in the beta release that will make it much easier soon. I think this could work:
=IF(ISBLANK([@Date]),0,
IF(ISNUMBER(SEARCH("hourly",[@[Fee Type]])),
"hourly",
SUM(
INDEX(
IFS([@Date]>=Pricing!D$6, Pricing!D$6:D$15,
[@Date]>=Pricing!E$6, Pricing!E$6:E$15,
TRUE, IF(ROW(Pricing!B$6:B$15),"Date Error")),
LET(in,D13, LL, LEN(C1),temp,SUBSTITUTE(in,",",REPT(" ",LL)),
inArray, TRIM(MID(temp,SEQUENCE(LL-LEN(SUBSTITUTE(in,",",""))+1,1,1,LL),LL)),
IFERROR(
XMATCH(inArray,Pricing!B$6:B$15),
XMATCH(inArray,Pricing!C$6:C$15))
)
)
)
)
)
without going too deep, this adds that LET statement that takes D13 and then splits it at every "," into a new string and creates an array of input called inArray. It then does the XMATCH on the array of values instead of only D13.
hope that help and really hope it works since I couldn't test it as a whole without the workbook.
EDIT:
I think that "Date Error" condition won't work. Probably have to 'pull that out' and check for it first. But I don't even know if I'm on the right track. Maybe let me know.
So it took me a minute to decipher your formula, as I'm still quite new to excel functions, but I think I've figured out a way to make it work. I had some trouble with the LET function, specifically in regards to "LL", as LEN(C1) returned 0 (at least in my workbook), which affected the rest of the calculation. I ended up googling "how to split a cell into an array" which resulted in this:
=1*(TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),IF(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)=1,1,(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)-1)*99),99)))
I then adapted it to fit my data and replaced your LET function with a slightly more simplified one. It's not quite as adaptable as yours, but it does the trick:
=IF(ISBLANK([@Date]),0,
IF(ISNUMBER(SEARCH("hourly",[@[Fee Type]])),
"hourly",
SUM(INDEX(
IFS([@Date]>=Pricing!D$6, Pricing!D$6:D$15,
[@Date]>=Pricing!E$6, Pricing!E$6:E$15),
LET(inArray,TRIM(MID(SUBSTITUTE([@Service],",",REPT(" ",99)),IF(SEQUENCE(LEN([@Service])-LEN(SUBSTITUTE([@Service],",",""))+1)=1,1,(SEQUENCE(LEN([@Service])-LEN(SUBSTITUTE([@Service],",",""))+1)-1)*99),99)),
IFERROR(
XMATCH(inArray,Pricing!B$6:B$15),
XMATCH(inArray,Pricing!C$6:C$15))
)))
)
)
I've also linked a workbook demo which I've removed most of the data from for reference. (please excuse the clunky formulas in other columns--I have yet to update them haha)
- I'm very happy you got it working and probably learned a bit too 🙂
That said, the statement that had an issue: LL, LEN(C1) was totally my bad and should have been LL, LEN(in)
The formula you used works as long as your input is <99 characters. Using LL, LEN(in) makes it so excel doesn't have to calculate that multiple times and is based on the actual cell length. Either way, both of these formulas will be 'out dated' soon once the new text functions are released to everyone.
With your new found knowledge I bet you will be cleaning up a lot of formulas