Search a grid of cells
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.
example:
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?
example:
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
=IFERROR(MATCH($B$18,$B2:$
Then Q2 and copied down:
=IFERROR($B$18/P2,"")
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.
Thanks
Rob H
Then cells P2 this formula and copied down:
=IFERROR(MATCH($B$18,$B2:$
Then Q2 and copied down:
=IFERROR($B$18/P2,"")
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.
Thanks
Rob H
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
Formula
=LARGE((ROUND($A$1/ROW(IND
- 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:
http://www.excelforum.com/excel-formulas-and-functions/488946-how-do-i-find-prime-factors-of-a-number.html
If you want to find the factors of a number, there is an array formula that can do it.
Number in A1
Formula
=LARGE((ROUND($A$1/ROW(IND
- 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:
http://www.excelforum.com/excel-formulas-and-functions/488946-how-do-i-find-prime-factors-of-a-number.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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,
Thanks
Thanks
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
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
ASKER
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.