2

I have the following formula

=CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 ))

The intention of this formula is to take a range and compare it to a list of unique identifiers on tab sheet concatmodbox which SHOULD look like this upon entering a formula (right now column D is empty):

DESIRED OUTPUT (COLUMN D):

enter image description here

Note that Column C is using a =UNIQUE formula to pull the info from boxmod column AC.

But my formula isn't working in column D

In tab sheet boxmod I have the unique IDS in column AC, and the data to append to them in columns I-AB. This sheet looks like:

enter image description here

However my formula cannot grab the entire range of I2:AB, so it only works if I write the formula for a single column. Does anyone know how to make a range work in the area {boxmod!I2:AB}?

  • pls share a copy of your sheet with an example of the desired output. its not clear what are you after exactly. you want to join columns I-J in column D based on match from column C? – user0 May 29 at 18:33
  • 1
    That is exactly what I'm trying to do, I added some clarification above – littlecoder May 29 at 18:59
0
  • cell C1:

    =UNIQUE(boxmod!AC2:AC)

  • cell D1:

    =ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
     VLOOKUP(C1:C, {boxmod!AC:AC, boxmod!I:J}, {2, 3}, 0))), , 999^99))))

    0

  • Can you further iterate on what the following does? {2, 3}, 0))), , 999^99 – littlecoder May 30 at 12:37
  • @littlecoder sure. {2,3} in vlookup means to select/output 2nd and 3rd column (from 2nd vlookup parameter/range). 0 in vlookup means "exact match". and 999^99 means a really big number so query could process that many rows – user0 May 30 at 14:10
  • 1
    That makes more sense, I was counting A as the start point and thought it should be {9,10}, but of course the actual start is where ever you start the vlookup on. Thanks! – littlecoder May 30 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.