Hi all, I would like to know which formula can I use to return the cell number if the citeria matched? e.g.

A1 = 1

A2 = 2

A3 = 3I would like to know the cell number where any cell in column A is equal to 1

Hi, I am not clear what you want.

In your example do you want the formula to return the value "A1", because cell A1 contains the value 1,

or

do you want the formula to return 1, because there is only one cell that contains the value 1.

or something else?When you say

any cell in column A is equal to 1, does this mean that there can be multiple cells in column A that contain 1,

if so, do you want the formula to return the number of cells containing 1.Regards

I wanted the cell number which is A1 not the content of the cell. If it is possible to return multiple cell reference number then it would be better.

I no need the number of cell containing 1.

Thanks

Hi, I used the following data starting in cell A1:

A 1 43 2 8 3 18 4 12 5 4 6 15 7 8

In another cell enter this formula:=MATCH(15,A1:A7,0)which returns the value 6, which is the position of the cell containing the number 15.

If you wanted the address of the cell containing 15 then you could use the following formula which includes the address of the first cell in the range:=ADDRESS(CELL("row",A1)+MATCH(15,A1:A7,0)-1,CELL("col",A1))This formula returned the value $A$6.Hope this was what you were looking for.

I can't think of a formula that would return multiple cell addresses. You could use COUNTIF() to test if there is more than one cell in the range with the value you want.

This formula returns the value 2:

=COUNTIF(A1:A7,8)Regards

Thanks, the formulas works if the matching citeria is number or text, but I am looking for error in the cell. In another word, I am looking for cell with "#Value!" or "#Ref!", and the formula cannot work already. I think is bcos of the "!" which cause the formula to have error. Anymore solution???

Hi, You didn't mention searching for a cell with an error value before!

Excel has a function that returns TRUE if the cell contains an error:

If cell B5 contains the error value #VALUE!, this formula=IF(ISERROR(B5),"Error", "No error")will return a text message "Error", but if B4 does not contain an error, it will return the text "No error".ISERROR(B5) on its own will return the logical values TRUE or FALSE

I am not sure what you mean by

I think is bcos of the "!" which cause the formula to have error.1. the ! is part of the error text, and it is not a separate character.

2. Which formula has the error. What does 'the formula' refer to?

3. An error message in a cell is there because the formula in the cell either has an error in it, or a cell it refers to has an error in it, or the function returns an error message by design, such as VLOOKUP when it does not find a match.

4. You can't search for error messages as though they are text. =IF(B5="#VALUE!,"Value error", "No Match") will not work even if cell B5 contains #VALUE!HTH

Regards

ok, thanks...

so =IF(ISERROR(B5),"Error", "No error") is able to detect error but it cannot return me the cell reference.What I need is to know the cell number. So if one of the cell in B columns got error I need which which cell got the error. So if B5 in column B got error I need to know is B5.

Hi, I don't know of a formula that will return the cell address of a cell containing an error.

Someone else may have an answer.

As a result I have written a user defined function specifically to return the address of a cell

containing an error.The function is FindErr()

It takes two arguments. The first is the range of cells to search and the second which is optional

and defaults to 1 is the incidence of a cell error.If cells A1 to A10 contain two cells with errors, A5 and A8, then:

=FindErr(A1:A10) will return "A5"

=FindErr(A1:A10, 2) will return "A8"

If there is no error in the range of cells, FindErr returns the #NA error.Here is the code for the function which should be placed in a standard module in your workbook:

Option Explicit Public Function FindErr(rngSearch As Range, Optional intErrOcc As Integer = 1) As Variant Dim rngCell As Range Dim n As Integer n = 1 For Each rngCell In rngSearch.Cells If IsError(rngCell.Value) Then If n = intErrOcc Then FindErr = rngCell.Address Exit Function End If n = n + 1 End If Next rngCell 'No error found so return NA error FindErr = CVErr(xlErrNA) End FunctionFindErr can be incorporated into other formulas.

Note that as this is a custom function and is in a module in the Workbook it willonlybe available

in that workbook, or copies of that workbook.Regards

The =CELL() function will return the cell address using: =CELL("address",A1)

So somthing along the lines of:

=IF(ISERROR(B5),CELL("address",B5), "No error")

might get you what your looking for.

MIKE

Thanks Humar...your solution may be a bit complicated but it works... Thanks mike as well although your solution is not the one i looking for.

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History