Need help finding cell addresses given specific criteria

[j_lafave87]

I work at a plant and need to take inventories on items. In one column, I need to be able to find values that are less than or equal to 15. I know enough to be able to find those values using COUNTIF, but I do not want the cell to display a number value. For example: =COUNTIF(N75:N10000, "<=15") displays "1." What I want to do is to be able to use this simple function but display the cell address(es) where this criteria is true. I guess I am looking for a formula that would use that criteria i gave, and output any cell addresses that are <=15. Say there were 4 cells that had values <=15, I want to search through them, and say N75, N80, N82, and N95 were <=15, I want to output them somewhere to make it easier to find these numbers in a large spreadsheet. Any ideas or hints would be much appreciated, thanks.

[NBVC]

Try:

=IFERROR(ADDRESS(SMALL(IF($N$75:$N$10000<=15,ROW($N$75:$N$10000)),ROWS($A$1:$A1)),6,4),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

[j_lafave87]

Try:

=IFERROR(ADDRESS(SMALL(IF($N$75:$N$10000<=15,ROW($N$75:$N$10000)),ROWS($A$1:$A1)),6,4),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

This helps, however when the rule is applied to my column of numbers, whatever ones are <=15 it only displays the first instance of this rule. Do you know how to make it display all instances of those cell addresses? Such as the one i find is N96 but N97 is also true to my rule so I would like to display "N96, N97" if this is possible?

[NBVC]

After you enter the formula, hold the Ctrl and Shift keys down, and hit Enter. You should see { } brackets appear around the formula. Then copy down the formula to get the remaining addresses....

[j_lafave87]

After you enter the formula, hold the Ctrl and Shift keys down, and hit Enter. You should see { } brackets appear around the formula. Then copy down the formula to get the remaining addresses....

It worked. Is there a quicker way to output these cells or do you have to do it one cell at a time? I guess I should have put more detail into my original post. Ok, is there a better solution where you can enter the formula and receive all of the cell addresses at once or would that take a lot more programming then what I want to do?

[NBVC]

What you can do is put the 15 in another cell and use that as your input cell.... then formula would be:

=IFERROR(ADDRESS(SMALL(IF($N$75:$N$10000<=$X$1,ROW($N$75:$N$10000)),ROWS($A$1:$A1)),6,4),"")

where X1 is your input cell.

Confirm formula with CSE keys and copy down as far as you need.

Then changing the input value will change the results.

Does that work?

[j_lafave87]

What you can do is put the 15 in another cell and use that as your input cell.... then formula would be:

=IFERROR(ADDRESS(SMALL(IF($N$75:$N$10000<=$X$1,ROW($N$75:$N$10000)),ROWS($A$1:$A1)),6,4),"")

where X1 is your input cell.

Confirm formula with CSE keys and copy down as far as you need.

Then changing the input value will change the results.

Does that work?

Yes, this seems to work a lot better. Thanks for the help and I think I will mark this as solved.