Forum Discussion
xlookup to combine multiple values in single cell
Hi, I'm updating my office billing sheet to auto-fill most information based on the information I've already put in. I'm using an office 365 subscription on windows 11. I want to use xlookup to return prices based on the service provided, and I've got it almost finished but I'm stuck on making it work when there are multiple services listed in the "services" column, separated by a comma. basically, it is currently programmed to pull prices from a chart in another sheet formatted as column 1: service name, column 2: service price, and return them to my billing sheet where I have the service provided listed under column D. It works great, but the lookup function doesn't recognize it as 2 services, as it is not an exact match to either, and I need it to see that there are 2 separate services and combine the prices for both. What is the most efficient way that I can have it search for all services in cell D and return the data accordingly? Ex: photos = $50, Prints = $25 ... cell D contains "photos, prints" but lookup returns "#N/A" when I need it to return "$75".
I feel like this might not be super helpful without reference to the sheet, but this is the formula I'm working with:
=IF(ISBLANK([@Date]),0,IF(ISNUMBER(SEARCH("hourly",[@[Fee Type]])),"hourly",IFERROR(IF([@Date]>=Pricing!D$6,XLOOKUP(D13,Pricing!B$6:B$15,Pricing!D$6:D$15),IF([@Date]>=Pricing!E$6,XLOOKUP(D13,Pricing!B$6:B$15,Pricing!E$6:E$15),"Date Error")),IF([@Date]>=Pricing!D$6,XLOOKUP(D13,Pricing!C$6:C$15,Pricing!D$6:D$15),IF(A13>=Pricing!E$6,XLOOKUP(D13,Pricing!C$6:C$15,Pricing!E$6:E$15),"Date Error")))))
It's referencing date as well as service, and finding prices in another sheet, for context.
I feel like I could use some combination of nested IFs with the SEARCH function, but that would likely end up being very long and hard to update as I make changes to pricing in the future.
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)
- mtarlerSilver Contributor
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.
- Chloe_52Copper Contributor
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)
- mtarlerSilver ContributorI'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
- Chloe_52Copper ContributorThanks! I'll give that a try and let you know if I run into any issues. Unfortunately, as the sheet contains a lot of personal/private data I can't just throw it all in a forum, but perhaps I'll try and re-create a demo workbook that could give you a better idea of what it looks like.