1

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

Improve this question
6
  • Welcome. Would you please advise how much data do you have (how many rows and columns). Would you please also advise, is this a one-off exercise, or something that is to be repeated (and if so, how often).
    – Tedinoz
    Jun 29, 2020 at 2:28
  • There is a match on O2, and on M3. So in this case I need a result like "2,3" Would you please explain where is this result to be recorded. If there was a match in Cell L3 and Cell N2, the result would also be "2,3" - Would you please explain how you would distinguish between these two identical results. Would you also clarify whether it is possible that the same code exists more than twice - that is, there might be three cells containing the same text.
    – Tedinoz
    Jun 29, 2020 at 2:37
  • Hi @Tedinoz, so sorry I see this just now. I expected I would get an email if anyone comments, but I had to return to the website to see this!
    – Rumi
    Jul 1, 2020 at 0:26
  • There could be only a single match per row, always. In my real data sheet the range I need to search is 9 columns and about 50 rows, but the rows can grow. Can't share the real thing, but I am trying to put together an example spreadsheet. And will post in a bit.
    – Rumi
    Jul 1, 2020 at 1:26
  • Regarding receiving emails when there are answers, comments and so on. Click on your Profile, Click on "Edit profile and settings". A menu list opens on the left of the screen. Under "Email settings", click on "Edit email settings". The options of the right-hand side of the screen will change. Under "Inbox", choose the email frequency that you want. I can't promise that this works each and every time cause I don't think I get over-whelmed by emails from StackExchange; however, I understand that this is the relevant setting.
    – Tedinoz
    Jul 1, 2020 at 2:04

1 Answer 1

Reset to default
0

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

Improve this answer
1
  • Amazing! THANK YOU so much @Tedinoz ! Works like magic, exactly what I needed!
    – Rumi
    Jul 3, 2020 at 1: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.