# Google Sheets formula to find rows with matching values, looking up in multiple columns

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!

• 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). Commented 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. Commented 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
Commented 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
Commented 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. Commented Jul 1, 2020 at 2:04

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

• Amazing! THANK YOU so much @Tedinoz ! Works like magic, exactly what I needed!
– Rumi
Commented Jul 3, 2020 at 1:22