Home > OS >  Google Sheets: Find a Row that Matches Only a Few Specific Characteristics
Google Sheets: Find a Row that Matches Only a Few Specific Characteristics

Time:10-23

I can't seem to find the right equation to find a cell from a row that matches only a few specific characteristics. In this example, I am trying to find the equation for Column D which would be the cell in A that has the same cells for B & C. enter image description here

Hope this makes sense!

CodePudding user response:

I'll provide two options.

If you're sure your data will only ever have zero or one match, you can place the following formula into D2 of an otherwise empty range D2:D...

=ArrayFormula(IF(A2:A="",,SUBSTITUTE(VLOOKUP(B2:B&C2:C,{B2:B&C2:C,A2:A},2,FALSE)&VLOOKUP(B2:B&C2:C,SORT({B2:B&C2:C,A2:A,ROW(A2:A)},3,0),2,FALSE),A2:A,"")))

However, if you think more than one match may turn up and you want "None" to be returned if there is no match, you can use the following formula in D2 or an otherwise empty range D2:D...

=ArrayFormula(IF(A2:A="",,REGEXREPLACE(REGEXEXTRACT(REGEXREPLACE(SUBSTITUTE(VLOOKUP(B2:B&C2:C,TRIM(SPLIT(FLATTEN(QUERY(QUERY({B2:B&C2:C&"~",A2:A&","}, "Select MAX(Col2) where Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)),"~")),2,FALSE),A2:A,""),"^[,\s] $","None"),"([^,\s]. [^,\s])[,\s]*$"),"[,\s] ",", ")))

The second formula will work even if there will only ever be zero or one match; it's just not necessary to have it be that lengthy. And the second formula is only as lengthy because it was unclear from your posted examples whether the data in Col A, B and C will really only ever be one word or not; so the formula is built to assume there will not always be one-word strings in those columns.

Either formula will provide results for the entire column without dragging.

CodePudding user response:

Here's an option, You can use this formula in column D2:

=iferror(textjoin(", ",true,query($A$2:$C,"Select A where A is not null and A != '"&$A2&"' and B = '"&$B2&"' and C = '"&$C2&"'",0)),"None")

Limitation:

  • You need to manually drag the formula to its succeeding rows. Arrayformula() cannot be used in looping the query string values.

What it does?

  • Using enter image description here

  • Related