Hi all,

I have a spreadsheet with data in the range of A9:M30. The data looks like
this:
588 - Number in a cell directly above a name
Hill - Name in a cell directly below a number
Each row has numbers in it and the row immediately below that one has a name
in it. I am looking for a way to search this range so for instance when
"Hill" is found, I want to return the value in the cell directly above it.
All names and the corresponding values above this will always be unique---no
duplicate names or numbers. Any suggestions would be greatly appreciated.
Thanks.

Ron

Here's one option:

=OFFSET(INDIRECT("A"&MATCH("Hill",A1:A100,0)),-1,0)

Replace "A" with the column letter that your data is in. You can also
replace "Hill" with a cell reference containing the value that you wish to
look up.

HTH,
Elkar


"Airfive" wrote:

Hi all,

I have a spreadsheet with data in the range of A9:M30. The data looks like
this:
588 - Number in a cell directly above a name
Hill - Name in a cell directly below a number
Each row has numbers in it and the row immediately below that one has a name
in it. I am looking for a way to search this range so for instance when
"Hill" is found, I want to return the value in the cell directly above it.
All names and the corresponding values above this will always be unique---no
duplicate names or numbers. Any suggestions would be greatly appreciated.
Thanks.

Ron

Here's one way.

In a VBA module in the same workbook, add the following function:

Public Function FindAddr(MatchCell As Range, SearchArray As Range) As String
Dim c As Range
On Error GoTo FAerr
For Each c In SearchArray
If c.Value = MatchCell.Value Then
FindAddr$ = c.Address
Exit Function
End If
Next c
FAerr:
FindAddr$ = Null
End Function

On your worksheet, in (for example) cell A1 enter Hill or whatever name you
want to search for. In B1, enter this formula:

=OFFSET(INDIRECT(FindAddr($A$1,$A$9:$M$30)),-1,0)

Hope this helps,

Hutch


"Airfive" wrote:

Hi all,

I have a spreadsheet with data in the range of A9:M30. The data looks like
this:
588 - Number in a cell directly above a name
Hill - Name in a cell directly below a number
Each row has numbers in it and the row immediately below that one has a name
in it. I am looking for a way to search this range so for instance when
"Hill" is found, I want to return the value in the cell directly above it.
All names and the corresponding values above this will always be unique---no
duplicate names or numbers. Any suggestions would be greatly appreciated.
Thanks.

Ron

Sorry for the delay getting back to say thank you for your help. I think I
am now on the right track...........Thanks for everyone's help.

Ron

"Tom Hutchins" wrote:

Here's one way.

In a VBA module in the same workbook, add the following function:

Public Function FindAddr(MatchCell As Range, SearchArray As Range) As String
Dim c As Range
On Error GoTo FAerr
For Each c In SearchArray
If c.Value = MatchCell.Value Then
FindAddr$ = c.Address
Exit Function
End If
Next c
FAerr:
FindAddr$ = Null
End Function

On your worksheet, in (for example) cell A1 enter Hill or whatever name you
want to search for. In B1, enter this formula:

=OFFSET(INDIRECT(FindAddr($A$1,$A$9:$M$30)),-1,0)

Hope this helps,

Hutch


"Airfive" wrote:

Hi all,

I have a spreadsheet with data in the range of A9:M30. The data looks like
this:
588 - Number in a cell directly above a name
Hill - Name in a cell directly below a number
Each row has numbers in it and the row immediately below that one has a name
in it. I am looking for a way to search this range so for instance when
"Hill" is found, I want to return the value in the cell directly above it.
All names and the corresponding values above this will always be unique---no
duplicate names or numbers. Any suggestions would be greatly appreciated.
Thanks.

Ron