Google-sheets – Google Sheets formula to find rows with matching values, looking up in multiple columns

formulasgoogle sheets

What would be the Google Sheets formula to search for a matching value in a range that goes across multiple rows an columns? For example I need to search the entire range H:P (all rows and columns) and find the cells with a matching value, if any. Ultimately in this case I need just a list of the row numbers where a matching cell is found. In the screenshot there are two matches highlighted in green. There is a match on O2, and on M3. So in this case I need a result like "2,3".

I have tried various things for several hours with no luck. Most examples of formulas that I could find and understand are about looking up in either a single column, or row.

Any help appreciated! Thank you!

range to be searched

Best Answer

Use this formula in cell D16 and copy down to cells D17 and D18.

=join(", ",
sort(
query(
{
  InStock!$C$2:$C,InStock!$B$2:$B;
  InStock!$D$2:$D,InStock!$B$2:$B;
  InStock!$E$2:$E,InStock!$B$2:$B;
  InStock!$F$2:$F,InStock!$B$2:$B;
  InStock!$G$2:$G,InStock!$B$2:$B;
  InStock!$H$2:$H,InStock!$B$2:$B;
  InStock!$I$2:$I,InStock!$B$2:$B;
  InStock!$J$2:$J,InStock!$B$2:$B;
  InStock!$K$2:$K,InStock!$B$2:$B
},
"select Col2 where Col1 = '"&C17&"'"))
)

It is not clear from the question whether the formula is to be used in a single cell, or in several cells (as in the example).

The queried ranges are open-ended (e.g. InStock!$C$2:$C,InStock!$B$2:$B;) - this allows additional data rows to be added to sheet="InStock" and that data will be included in the query.

Example Output

Example output

Related Topic