Help with matching cell and returning adjacent value

[Shanks100]

Hi,

I have a worksheet with data from D2:DL2989, all unique and formatted as general.

In cell A1 I input a value which matches one of those cells. What I would like is to have cell B1 display the contents of the cell adjacent in the next column of the cell in D2:DL2989 that matches A1.

I tried Vlookup but as I cannot put it all into two columns it won't work. I think I need a mix of Index and Match but cannot seem to get the formula correct.

Any help would be appreciated, Mike

[AliGW]

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[shukla.ankur281190]

May be try

B2

Please Login or Register  to view this content.

[Shanks100]

Thanks Shukla, but i think that wil only give me a search range of one column. The data I'm trying to match is from D2: DL2998.

Thanks for the suggestion

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JohnTopley]

Try

=INDEX($D$2:$DL$2989,SUMPRODUCT(($D$2:$DL$2989=A1)*(ROW($D$2:$D$2989)))-1,SUMPRODUCT(($D$2:$DL$2989=A1)*(COLUMN($D$2:$DL$2)))-2)

[Shanks100]

Hi thanks for your help. I have uploaded a simplified sample sheet.

There are a number of blank cells, but these will fill over time.

What I'm lookin for is to enter a value in cell O2 and then have the formula search the range D2:M35 and have cell P2 display the cell value adjacent to that matching value, so in this example P2 would display 00:19:19 from M10.

thanks

[JohnTopley]

See attached using formula of format in post #6

I had to re-enter the data in N10 for MATCH to be found .

[Shanks100]

John, thank you very much, a little tweak and it's exaclty what I needed. I hadn't factored for SUMPRODUCT and if I'm honest I wil now go and look at some hlpefiles to understand that aspect if i can.

But it now works as i hoped.

Thanks again for your time and assistance, Mike

ps this was the final line that worked:

=INDEX(pbs_range,SUMPRODUCT((pbs_range=AI2)*(ROW(pbs_range)))-1,SUMPRODUCT((pbs_range=AI2)*(COLUMN(pbs_range)))-2)

[shukla.ankur281190]

Or try

Please Login or Register  to view this content.

Check the attached file.