Forum Discussion

Chloe_52's avatar
Chloe_52
Copper Contributor
Aug 15, 2022
Solved

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 retur...
excel
Formulas and Functions
office 365
  • Chloe_52's avatar
    Chloe_52
    Aug 22, 2022

    mtarler 

    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)

mtarler's avatar
mtarler
Silver Contributor
Aug 15, 2022

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_52's avatar
Chloe_52
Copper Contributor
Aug 16, 2022
Thanks! 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.

Resources