Return entry in column above/below cell

[TheRobsterUK]

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

[Ron Coderre]

Try this:

With your list of animals is in Column A (no repeats) beginning in A2
A1: Animals
A2 through A???: List of animals

B1: (User inputs an animal)
or...even better
Select B1
Data>Data Validation
\Allow: List
\Source: A2: (end of your list)

For item before selection:
B2: =INDEX(A:A,MATCH(B1,A:A,0)-1,1)

For item after selection:
B3: =INDEX(A:A,MATCH(B1,A:A,0)+1,1)

Does that help?

••••••••••
Regards,
Ron


"TheRobsterUK" wrote:

>
> 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
>
>
> --
> TheRobsterUK
>
>
> ------------------------------------------------------------------------
> TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
> View this thread: http://www.excelforum.com/showthread...hreadid=485713
>
>

[TheRobsterUK]

Hi Ron,

Yes I got it working with some slight adaptations to your suggestion. Many thanks for the response.

-Rob