google-sheetsgoogle-sheets-formula

find a column where specific rows contain specific values

I'm trying to create a function that will allow me to find a column where specific rows contain specific values, and return the value of row 1 for all columns that contain those specific values

I'm not sure what to try, this is a sample data:

Job 1 Job 2 Job 3 Job 4 Job 5 Job 6
Type Small Medium Medium Large Small Large
Colour Red Blue Green Green Red Yellow
Style Round Square Scallop Square Round Square
Flavour Apple Banana Orange Banana Apple Apple

I'm needing to find as an example, results that match a set of the first column variables (Type, Colour, Style, Flavour) and for each column that contains the values, return the job number (row 1 value), for example if I wanted to find Type: Small and Flavour: Apple, the formula should return Job 1 & Job 5

It might not always be Type and Flavour, it could be 2 or more variables to match and return the job number for the matching results

Solution

  • find Type: Small and Flavour: Apple, the formula should return Job 1 & Job 5

    Use filter(), like this:

    =filter(B1:Z1, "Small" = B2:Z2, "Apple" = B5:Z5)

    To match by different rows, it would be easiest to put each selection criteria in a dropdown cell of its own, and use additional instances of filter() to choose the row within the formula above, like this:

    =filter( 
  B1:Z1, 
  B10 = filter(B2:Z5, B9 = A2:A5), 
  C10 = filter(B2:Z5, C9 = A2:A5)
)

    The formula expects to find criteria in cells B9:C10. To add more conditions, continue adding lines like D10 = filter(B2:Z5, D9 = A2:A5).

    To ignore criteria that has been left blank, use this pattern:

    =filter( 
  B1:Z1, 
  (B10 = "") + (B10 = filter(B2:Z5, B9 = A2:A5)), 
  (C10 = "") + (C10 = filter(B2:Z5, C9 = A2:A5)), 
  (D10 = "") + (D10 = filter(B2:Z5, D9 = A2:A5)) 
)

    screenshot

    See filter().