asked on

Searching for an occupied cell

Here's my problem, given the following info about my Excel spreadsheet.

A1 B1
A2 B2
A3 B3
A4 B4

I need B4 to compute a value based on the previous. The problem is that I don't know if there is a value in the previous column, so I have to search up the column until I find a value. So here's the scenario:

B4 computes a value based on the first occuppied cell in Column A. So, starting at A3 it goes up the column, and looks for the value.

How do I search for an occupied cell?

byundt

Hi loneill2,
You can use an array formula to return the first unoccupied cell in column A (looking up) like:
B4=INDEX(A$1:A3,MAX(IF(A$1:A3<>"",ROW(A$1:A3),0)),1) Array formula, so hold Control + Shift down while hitting Enter. Excel will respond by adding curly braces { } surrounding the equation.

Cheers!

Brad

Dave

This will find the value in Column A - it will ignore any strings that may occur.

=LOOKUP(1E20,A:A)

Cheers

Dave

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

loneill2

ASKER

The problem with LOOKUP is the first argument requires me to know what the value is.

I don't know what the value is in the column. I just want to find the very first column that has a non-zero value.

Dave

This method will find your last cell in A with a value - it isnt intending to match a particular value

Cheers

Dave