Search array and return element No

[Ron]

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

 

[Biff]

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]

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

 

[Biff]

=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:

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]

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

 

[Biff]

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

 

[Ron]

Powerful stuff... Thanks.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff