How do I select the first occurance of a certain number in a column and then,
as in the "Lookup" function, return a same numbered cell (different column).
I have "0's" and "1's" in my column (intermixed) and I am looking for the
first occurance of a "1". I then want to look up another value in the same
row (different column). "Lookup" function doesn't work if you have multiple
values not sorted. I'm using Excel 2003.

=MATCH(1,C:C,FALSE)
This returns the first occurance in column C of the number 1...in my case th
eresult was 5, and row was also row 5 since my data started at row 1
If the match function is in B1, then in say B2 put

=OFFSET(G1,B1-1,0)

this uses the result of the match to get an item from column G that matches
the row indicated....the '-1' is the adjustemnt for the offset. For example
if we need row 5, then G1 offset 5 points at G6, so we reduce the offset by
one G1 offset(5-1) now points to G5




"Corner920" wrote:

How do I select the first occurance of a certain number in a column and then,
as in the "Lookup" function, return a same numbered cell (different column).
I have "0's" and "1's" in my column (intermixed) and I am looking for the
first occurance of a "1". I then want to look up another value in the same
row (different column). "Lookup" function doesn't work if you have multiple
values not sorted. I'm using Excel 2003.

Hi Corner920,

You don't have to select the searched cell in order to get the value. Try
something like this:

Sub Test()
With Columns("A:A")
MsgBox .Find( _
What:=1, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:= _
xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext) _
.Offset(0, 1).Value
End With
End Sub

or a slightly shorter version:

Sub Macro5()
With Columns("A:A")
MsgBox .Find(1, .Cells(1), xlValues, _
xlWhole, xlByRows, xlNext).Offset(0, 1).Value
End With
End Sub

Regards,
KL

"Corner920" wrote in message
...
How do I select the first occurance of a certain number in a column and
then,
as in the "Lookup" function, return a same numbered cell (different
column).
I have "0's" and "1's" in my column (intermixed) and I am looking for the
first occurance of a "1". I then want to look up another value in the
same
row (different column). "Lookup" function doesn't work if you have
multiple
values not sorted. I'm using Excel 2003.