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}?

2
  • 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
    Commented May 29, 2019 at 18:33
  • 1
    That is exactly what I'm trying to do, I added some clarification above Commented May 29, 2019 at 18:59

1 Answer 1

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

3
  • Can you further iterate on what the following does? {2, 3}, 0))), , 999^99 Commented May 30, 2019 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
    Commented May 30, 2019 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! Commented May 30, 2019 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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