Loop through an array in a single worksheet cell

Question

I am trying to calculate an amount based on a formula that has a different number of arguments for each calculation. And, each formula is expressed as a string that is based on the column names at the top of the sheet (e.g. item1). Each argument feeds a XLOOKUP to get the value and then that value gets added to the one before it (a+b+c+d).

            |item1|item2|item3|item4|item5|
itemAmount    100    50    25     2    3
rate           ?     ?      ?     ?    ?

formula for item1 rate = item1
formula for item2 rate = item1, item2
formula for item3 rate = item3
formula for item4 rate = item3, item4

formulas are expressed in a comma-separated array in a cell in the worksheet.

Desired Results:
rate for item1 = 100
rate for item2 = 150
rate for item3 = 25
rate for item4 = 27
rate for item5 = 180

The below works until I have 4 or more arguments to add together. e.g. item1, item2, item3, item4 will not get calculated.

Is there a way to implement this that truly loops through the formula arrays? I tried REDUCE() but that didn't seem to recognize text.

What I have so far is a combination of LAMBDA() and LET(). I used LAMBDA() to create a reusable formula for the users that will get inserted in dozens of places in the workbook.

=LAMBDA(
TaskFormula,TaskNamesRange,RateName,RateNamesRange,ReturnRange, 

LET( 
tfl, 
TaskFormula, 

tnrng, 
TaskNamesRange, 

rateType,
TRIM(RIGHT(RateName,LEN(RateName)-FIND(">> ",RateName)-1)), 

rateNameRng, 
RateNamesRange, 

retRng, 
ReturnRange, 

a, 
XLOOKUP(IFERROR(TRIM(LEFT(tfl,FIND(", ",tfl)-1)),tfl),tnrng,XLOOKUP(rateType,rateNameRng,retRng)),  

b, 
IFERROR(XLOOKUP(TRIM(SUBSTITUTE(MID(SUBSTITUTE(","&tfl&REPT(" ",6),",",REPT(",",255)),2*255,255),",","")),tnrng,XLOOKUP(rateType,rateNameRng,retRng)),0),  

c, 
IFERROR(XLOOKUP(TRIM(MID(tfl,FIND("#",SUBSTITUTE(tfl,",","#",2))+1,255)),tnrng,XLOOKUP(rateType,rateNameRng,retRng)),0),  

a+b+c))

Prefer no VBA if possible. Using Win10, Excel 365 Monthly Enterprise channel.

Scott Craner · Accepted Answer · 2022-08-26 21:52:22Z

1

Use SUMIFS with TEXTSPLIT:

=LAMBDA(TaskFormula,TaskNamesRange,ReturnRange,SUM(SUMIFS(ReturnRange,TaskNamesRange,TEXTSPLIT(TaskFormula,", "))))

TEXTSPLIT may not currently be available to all. In that case use FILTERXML in its place:

=LAMBDA(TaskFormula,TaskNamesRange,ReturnRange,SUM(SUMIFS(ReturnRange,TaskNamesRange,FILTERXML("<t><s>"&SUBSTITUTE(TaskFormula,",","</s><s>")&"</s></t>","//s"))))

Or this one that does not split the formula but looks to see if the item is included in the formula:

=LAMBDA(TaskFormula,TaskNamesRange,ReturnRange,SUM(BYCOL(SEQUENCE(,COLUMNS(TaskNamesRange),1,1),LAMBDA(a,IF(ISNUMBER(SEARCH(INDEX(TaskNamesRange,,a),TaskFormula)),INDEX(ReturnRange,,a),0)))))

edited Aug 26, 2022 at 21:52

answered Aug 26, 2022 at 21:39

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Bennett Over a year ago

Thanks Scott! I'll try this out. TEXTSPLIT() is not available for me just yet so thanks for the other options. I'll try them out this week.

Collectives™ on Stack Overflow

Loop through an array in a single worksheet cell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related