Find #VALUE! using MATCH formula

[BigDelGooner]

Hi all

I am trying to return the first row in a range where the value is #VALUE!.

I am currently using the following formula but it doesn't seem to be working... =MATCH(ERROR.TYPE(3),Indices!$Z:$Z,0).

It works when I am searching for a text string, say.... =MATCH("TEST",Indices!$Z:$Z,0).

Any ideas?

 

[barry houdini]

Try this array formula

=MATCH(TRUE,ERROR.TYPE(Indices!$Z:$Z)=3,0)

confirmed with CTRL+SHIFT+ENTER

In versions of Excel before 2007 you can't use the whole column, e.g. in Excel 2003 replace Indices!$Z:$Z with a specific range like Indices!$Z2:$Z100

 

[T. Valko]

Hi all

I am trying to return the first row in a range where the value is #VALUE!.

I am currently using the following formula but it doesn't seem to be working... =MATCH(ERROR.TYPE(3),Indices!$Z:$Z,0).

It works when I am searching for a text string, say.... =MATCH("TEST",Indices!$Z:$Z,0).

Any ideas?

Assuming that #VALUE! is the only error value in the range.

This array formula**:

=MATCH(TRUE,ISERROR(Z1:Z20),0)

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Note that unless you're using Excel 2007 or later you can't use entire columns as references in certain types of array functions.

 

[BigDelGooner]

Thank you v much guys.

I was struggling at first because I was entering the entire 'Z' column which Excel doesn't seem to like. However if you specify the Z column but rows 1 to 65535 (instead of 65536) it work fine.

Thanks again.

 

[T. Valko]

Thank you v much guys.

I was struggling at first because I was entering the entire 'Z' column which Excel doesn't seem to like. However if you specify the Z column but rows 1 to 65535 (instead of 65536) it work fine.

Thanks again.

You're welcome. Thanks for the feedback!