finding a value on another worksheet in the same workbook

[Dan Wilson]

Good day. Grab a cold one, this is a long one. I am using Excel 2016 on Windows 10 Home. I have created a workbook containing 7 worksheets. One of the worksheets is called Rides and contains a list of rides taken by my club. Another worksheet is called RC and contains a list of Ride Captains who planned and led each ride. Here is the layout:

Rides worksheet
Column A - Date of the ride
Column B - Ride number
Column C thru J - incidental data about each ride
Column K - name of Ride Captain for each ride (this is where I need help)
There are 100 rows on this worksheet to allow data for 100 rides.

Ride Captains worksheet
Column A - Ride Captains names
Column B thru L - incidental data about ride totals
Column M thru DH - cells to contain the letter "L" to indicate who led the ride (allows data for 100 rides).
There are 35 rows on this worksheet to track data about each ride captain.
Column M is ride 18-01, Column N is ride 18-02, Column O is ride 18-03, etc. The "18" indicates the year.
Each Ride Captain should only have one column from M thru DH containing the letter "L" indicating that they were the Ride Captain for that ride.

Here is the setup:
Dan Wilson is the Ride Captain who led ride 18-04, thus on the Ride Captain worksheet, Row 15 (alpha sort by name), Column P (Ride 18-04) would contain the letter "L". On the Rides worksheet, Row 5 (date sort), Column B (number sort) will contain the Ride Number (18-04).

Here is the problem:
I have tried using VLOOKUP, but all I can get is either the Ride Number or the "L" as a result. Is there a way to put a formula in Column K, Rows 2 thru 101 on the Rides worksheet that will identify the Ride Captain for each Ride Number from the Ride Captain worksheet?

Thank you for any help, Dan Wilson...

 

[Yongle]

Hi Trevor and Yongle

Try to avoid OFFSET and INDIRECT, volatile functions, whenever it's possible. See the formula in post 4.

@Marcelo Branco
Thank you for the advice. But I do not understand why you you say that on this thread. OP's worksheet is very small (and will not grow)
Why should those functions be avoided for this solution?

 

[Marcelo Branco]

In this case you may be right, but what I have tried to say is that it is generally best to avoid volatile functions - anyway if there is a solution without volatile functions, why use them?

M.

 

[Dan Wilson]

Thank you, Dan Wilson...

 

[Dan Wilson]

Good day Yongle. BINGO! Your fix works! Thank you for responding. I LOVE this forum. It has bailed me out of trouble many times.
Thank you, Dan Wilson...

 

[Dan Wilson]

Thank you to everyone who responded to this issue. I learn something every time I get on this forum.
Dan Wilson...