*Resolved* Excel: return entry in column above/below cell

Hi,

I need to find a way of returning the cell values that are directly above and below a specific cell. For example, suppose I have the following list in column A:

dogs
cats
fish
mice

There is another cell (say cell B1) in which the user inputs one of the items in the list and then another 2 cells (say B2 and B3) that return the entries above and below the user-input entry i.e. in this case, the user input "cats" and so in cell B2 would be displayed "dogs" and in cell B3 "fish".

I cannot get this to work however. I have tried several functions (e.g. HLOOKUP) but it does not work as intended. Can anyone give me any help/advice?

Many thanks
-Rob

if you would like a vba solution:

record a search for the value that the user inputs within column A.

then use the offset command to get the values you need.

VB Code:

ActiveCell.Offset(1,0).Activate ' will return next row, same column
ActiveCell.offset(-2,0).activate ' will return previous cell, same column

HTH

If you want to do this without using VBA then the following formulas will work. I have assumed that the user enters their value in cell B8.

Cell B2 = Prior Value in list
Formula =INDEX(A:A,MATCH($B$8,A:A,0)-1,0)

Cell B3 = Next Value in list
Formula =INDEX(A:A,MATCH($B$8,A:A,0)+1,0)

Got it working using DKenny's method. Thanks to both of you for the responses. :)

Cheers
-Rob