Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querytextjoin

Pulling a range based on one criteria

I have a formula that allows me to join multiple rows together and remove duplicates:

=TEXTJOIN(" ", 1, UNIQUE(TRANSPOSE(FILTER(Sheet1!A:D, NOT(REGEXMATCH(LOWER(Sheet1!A:D), "n/a"))))))

The issue I'm running in to is because this references another tab in the same sheet, it cannot be sorted by different columns without changing the results.

So what I'd like to know is: how do I modify the above formula so that it pulls the data it needs based on criteria I give it? Note this will need to be across a range of cells and includes strings of text.

Solution

  • After your comment I understand what you are trying to do. Basically you want to retrieve an information of the first sheet matching with your "row key".

    Is there any reason why you haven't used a VLOOKUP?

    For example using this formula:

    =IFNA(VLOOKUP(A1,Sheet1!A:G, 7, FALSE), "VALUE NOT FOUND")

    Example of Use

    If you sort or randomize the range of column B you get a result like this:

    Randomize List

    In here you see that every description keeps up with its key.

    EDIT: So after your comment it seems that you don't have your information inside a single cell. You could create this cell as a placeholder to later retrieve the information.

    Or if you need you could make this inside the formula, that could make this a little messy but still could be workable. 

    =TEXTJOIN(" ", 1, 
    UNIQUE(
        TRANSPOSE(
            FILTER({VLOOKUP(A1, Sheet1!A:G, 3, FALSE), VLOOKUP(A1, Sheet1!A:G, 4, FALSE), VLOOKUP(A1, Sheet1!A:G, 5, FALSE), VLOOKUP(A1, Sheet1!A:G, 6, FALSE)},
                   NOT(LOWER({VLOOKUP(A1, Sheet1!A:G, 3, FALSE), VLOOKUP(A1, Sheet1!A:G, 4, FALSE), VLOOKUP(A1, Sheet1!A:G, 5, FALSE), VLOOKUP(A1, Sheet1!A:G, 6, FALSE)}) = "n/a")
            )
        )
    )
)

    Created Range