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" wrote in message
oups.com...
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" wrote in message
ups.com...
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" wrote in message
ups.com...
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