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

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

1 Answer 1

Reset to default
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

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

Your Answer

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

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