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?

question from:https://stackoverflow.com/questions/65834654/excel-office-365-concat-and-2x-vlookup-to-generate-a-unique-string

Answer 1

You can use MATCH and the extended form of OFFSET to do what you want.

MATCH will find a matching value, rather like VLOOKUP but it returns the location of the matching value rather than the value itself (eg if you fed it the range X2:AA257 and it found the value in row 10, it would return 9).

OFFSET will return the value in a cell a certain distance away from the origin cell you give it, eg =OFFSET($C$1,5,10) will return the value in cell H11 (five rows and 10 columns away from cell C1). In its extended form you can give it a width and height, eg =OFFSET($C$1,5,10,2,2) will give you the range H11:I12.

You can then feed the output from OFFSET into CONCAT and it will give you what you want.

Bonus tip: TEXTJOIN does everything CONCAT does, but you can also give it a delimiter - eg =TEXTJOIN(", ",0,A1:A10) will return the values in the ten cells with a comma and space in between each one.