We help IT Professionals succeed at work.

Search a grid of cells

Enabbar Ocap
Enabbar Ocap asked
I'm looking for a search function to return the row and column of a cell containing matched data, or a combination of worksheet functions to do the same thing.



set up numbers 1 to 12 across B1 to M1, and in A2 down to A13.

enter formula in B2 to M13 to multiply the number in row 1 by the number in column A.

Now I have a range B2:M13 containing all the various multiples.


A countif function tells me that there are 6 appearances of the number 12 in this range.


I want to be able to find the column and row numbers (actually the numbers from the top row and the left column), where these 12s appear so I can use the values in further calculations.


The Find & Select engine returns the cells using the Find All, but I want to be able to search over the range with a function.

As there is more than one match, either returning the first one or being able to add a 1st, 2nd, 3rd etc parameter would be nice.


Is there any worksheet function to search over a range of cells?
Watch Question

"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
The attached workbook contains this macro.
Sub FindAllNumber()
Dim intToFind As Integer
Dim cel As Range
Dim intRow As Integer

intToFind = InputBox("Please enter the number to be found", "Enter Number", 12)
intRow = 15

For Each cel In Range("B2:M13")
    If cel.Value = intToFind Then
        Cells(intRow, 1) = cel.Address
        intRow = intRow + 1
    End If

End Sub

Open in new window

Give it a try.
It finds them all. Is there a tidy way to get that to work as a function? I could hack it about but you might have something close already.

Guessing it needs a separate row and column function and an input parameter for the instance required.

If it's too much to ask just say so. If it requires a lot of coding then I'd be better approaching the while project as a coding exercise rather than a spreadsheet.
Rob HensonFinance Analyst

Assuming with the same setup as described in your question, put the value that you are looking for in cell B18.

Then cells P2 this formula and copied down:


Then Q2 and copied down:


Columns P & Q now give the grid references for the value you are looking for. They could be combined into one result if so required with a colon separator.

However, I suspect the situation as described above is purely for illustration purposes, and the above is too simplistic.

Rob H
Danny ChildIT Manager

This strikes me as a bit of unscrambling an egg...

If you want to find the factors of a number, there is an array formula that can do it.

Number in A1

 - and press CTRL + SHIFT + ENTER to make it an array formula.  Then, copy downwards for as many cells as you have factors.

More info here:
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
One of the above solutions may well be better than mine but try this.

Put the formula =FindAllNumber(12) in any cell. Note that "12" could be any number.
Thanks all. I am away from my machine until tomorrow.  It is indeed unscrambling an egg,  but not unfortunately as simple as factoring. This is just to explain what I am looking for. The full lump covers at the moment a grid of 1000 by 1000 and is likely to probably quadruple if the processor can keep up. I am also checking for more than one number so copying down something to check every line is not really ideal.
Martin's first macro basically does the job and with a bit of tweaking ( which may be just above) should provide the function I need.
I was surprised not to find an existing function to search a block, the count functions are happy to read more than a column or row.
I'll give it a try tomorrow and come back.
Thanks Martin. I'll twiddle it a little, but that finds them ok. I'm going to bend it into a function something like FindNo(numbertofind,range,instance,[row|col]) to return into the cell where I put the function so I can use the returned values directly into other functions. Your work has given me a good start. It does take a strangely long time to run, but that might be because my machine is busy doing other things as well.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014