range - Excel Office 365, Concat and 2x VLookup to generate a unique string

Question

I'm trying to create a string of text by combining all cells between two variable limits.

This function is effectively what I want, but it doesn't work in Excel:

=CONCAT(VLOOKUP("Yes",X2:AA257,4,0):VLOOKUP("Yes",Y2:AA257,3,0))

I did have a variant that used a macro to force conversion of a string of vlookup'd cell addresses to a formula and evaluate that, but it is clunky and slow, and using the macro makes it harder to share the file with other people.

I know that both these functions work:

 - =CONCAT(AB2:AB257)
 - =CONCAT(VLOOKUP("Yes",X2:AA257,4,0),VLOOKUP("Yes",Y2:AA257,3,0))

so why doesn't the combined version with the colon instead of the comma?

The 'start string' instruction variable is in column Y, the 'end string' instruction variable is column Z, and the data that assembles to form the string is in column AA. Notably, the function I'm after makes use of VLOOKUP returning the result for the first match and ignoring everything below that which is how I'm handling the variable string length - the string might need to draw from anywhere between four and sixty cells so far, and the highest value I've seen is 233 cells so 255 seems a reasonable future proof.

I suppose another option would be to have 255 sets of ...,IF(VLOOKUP... in the CONCAT brackets but that's even harder to troubleshoot if and when something goes wrong, and I'm not sure how I'd define the end of one string-range and the start of the next one; maybe stacked IFs, with more complexity penalties?

Answer 1

等待大神解答