Inserting TextJoin results into VLookUp formula

[helpcharity]

Help please,

I have data for the performance of person during a 12 month period, in a worksheet named after them.

I want to create a total's sheet which shows how much fruit each person has picked for certain selected months of the season.

The current forumla works fine: =SUMPRODUCT(VLOOKUP($B10,INDIRECT(F$9&"!"&"$B:$Q"),{6,7,9,10},0))

But I want to replace the text "{6,7,9,10}" with the contents of another cell which uses the TextJoin formula to create "6,7,9,10".

I tried: =SUMPRODUCT(VLOOKUP($B10,INDIRECT(F$9&"!"&"$B:$Q"),INDIRECT("{"&C5&"}",0)))

But this just generates an #REF message

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[helpcharity]

I'm using (desktop) Office Professional 2019. I also have access to "Microsoft Office 365 E1".

 

[Fluff]

Ok, for 365 you could use

=SUMPRODUCT(VLOOKUP($B10,INDIRECT(F$9&"!"&"$B:$Q"),textsplit(C5,","),0)))

for 2019 what is your textjoin formula

 

[helpcharity]

I used: =TEXTJOIN(",",TRUE,E5:P5)

 

[Fluff]

Ok, try

=SUMPRODUCT(VLOOKUP($B10,INDIRECT(F$9&"!"&"$B:$Q"),AGGREGATE(15,6,$E$5:$P$5/($E$5:$P$5<>""),TRANSPOSE(ROW(INDIRECT("1:"&COUNTIFS($E$5:$P$5,">0"))))),0))

This may need to be confirmed with Ctrl Shift Enter.

 

[helpcharity]

That's worked perfectly! Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback.