Search array and return element No

I want to seach an array and return the element no of the first cell
that is empty. Vlookup does not work as it returns the value as opposed
to the element no.

I want to do something like Find except accross an array rather than
within a string.

Any ideas

Thanks

Hi!

Try this:

Array entered using the key combination of CTRL,SHIFT,ENTER:

=MATCH(TRUE,A1:A15="",0)

Returns #N/A if no blank cell is found.

Biff

"Ron" <[email protected]> wrote in message
news:[email protected]...
>I want to seach an array and return the element no of the first cell
> that is empty. Vlookup does not work as it returns the value as opposed
> to the element no.
>
> I want to do something like Find except accross an array rather than
> within a string.
>
> Any ideas
>
> Thanks
>

This worked....sort of. When I look at the values by pressing the
function button on the tool bar it has the correct value (3,5 etc) but
once I click out of the cell the cell display #VALUE. My exact formula
is

=MATCH(TRUE,Sheet1!$R8:$AD8=0,0). The formula is in a Cell in sheet 2.

Thanks for you help

>=MATCH(TRUE,Sheet1!$R8:$AD8=0,0)

A couple of things, you didn't say what type of data you have in this array.
So, you can use 0 to mean an empty cell but if there is a literal 0 in the
range before an empty cell you'll get an incorrect result.

The reason you're getting #VALUE! is because this is an array formula:

>>Array entered using the key combination of CTRL,SHIFT,ENTER:

Try this:

Select the cell where this formula is entered.
Press function key F2
Now, hold down both the CTRL key and the SHIFT key then hit ENTER.

If done properly Excel will place squiggly braces { } around the formula.
You cannot just type these braces in. You MUST use the key combination.

Biff

"Ron" <[email protected]> wrote in message
news:[email protected]...
> This worked....sort of. When I look at the values by pressing the
> function button on the tool bar it has the correct value (3,5 etc) but
> once I click out of the cell the cell display #VALUE. My exact formula
> is
>
> =MATCH(TRUE,Sheet1!$R8:$AD8=0,0). The formula is in a Cell in sheet 2.
>
> Thanks for you help
>

Thanks Biff this worked great. Can you just explain what an array
formula means

"Ron" <[email protected]> wrote in message
news:[email protected]...
> Thanks Biff this worked great. Can you just explain what an array
> formula means
>

An array formula is a formula that operates or performs tests on more than a
single element. In this case the array test is:

Sheet1!$R8:$AD8=0

Each cell in the range R8:AD8 is being tested to be equal to 0.

See this for more info:

http://cpearson.com/excel/array.htm

Biff

Powerful stuff... Thanks.

You're welcome. Thanks for the feedback!

Biff

"Ron" <[email protected]> wrote in message
news:[email protected]...
> Powerful stuff... Thanks.
>