Need to look up a value within a column then display the value of the cell below

[brappatta]

I have two Excel worksheets: Worksheet 1 and Worksheet 2. I need to put a formula in Worksheet 2 which tells it to look in a specific column in worksheet 1. Then, when it finds that particular value it's looking for, I need the cell to display the contents of the cell BELOW the value it found from that specific column in Worksheet 1.

Some 3D formula combined with the OFFSET formula, perhaps? I'm stymied.

Edited, with attachments:

In the 5D4N worksheet, for example, I need in Cell e7: I need to have the formula look for the value "1" in the 5D4N (2) worksheet wherever that value might occur in column E, then return the value of the cell above or below where it found that value.

[satputenandkumar0]

Dear brappatta welcome to forum,

pls attach the sample files.

Regards,
Nandkumar Satpute

[TMS]

Use INDEX/MATCH ... whatever the Match returns, add 1 to it to get the next row.


Regards, TMS

[satputenandkumar0]

Use INDEX/MATCH Formula

[brappatta]

I've tried using the index/match formula, but I'm not savvy enough to get the formula quite right.