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...

 

[Coding4Fun]

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?

What if you were to just use the person's name in place of the letter L, it looks like you can get it to return the "L" change this to the persons name?

I am not sure you will be able to achieve what you are trying to do with your current set up.

 

[Dan Wilson]

Good day coding4fun. Thank you for responding. Unfortunately it is the name that I am trying to retrieve. The name does not exist on the Rides page and that is where I am trying to import it to. the only common data between the two pages is the Ride Number.
Dan...

 

[Marcelo Branco]

Maybe...

K2 of Rides worksheet copied down
=INDEX('Ride Captains'!A:A,MATCH("L",INDEX('Ride Captains'!M:DH,0,MATCH(B2,'Ride Captains'!$M$1:$DH$1,0)),0))

Hope this helps

M.

 

[Trevor_S]

I think it is possible, using a combination of nested match and offset functions. I'm on my phone at the moment, so won't be able to try it today, but I'll try to explain my theory in case it helps you or someone else work it out first...
A Match formula could return the column position relating to that row's ride. For example, it could tell that 18-04 was in the 16th column (column P) of the header row of the ride captains sheet. That could be used within another match formulae, which looked for an L within column A, offset by the result of the first match formula, minus one. This would return the row number on the ride captains sheet of the captain for that ride, and their name would be in column A of that row...

Assuming that the sheets are called R and RC, and the RC sheet row 1 contain the ride numbers, try something like:
=INDIRECT("$A"&MATCH("L",OFFSET('RC'!$A:$A,0,MATCH($B#,'RC'!$1:$1,0)),0))
where # is the number of the row that you're entering the formula on. But I haven't been able to test this yet!

 

[Trevor_S]

Spotted a problem with the formula above, should be:
=INDIRECT("'RC!'$A"&MATCH("L",OFFSET('RC'!$A:$A,0,MATCH($B#,'RC'!$1:$1,0)-1),0))

 

[Yongle]

In sheed Rides K2 copy down
=INDEX('Ride Captains'!A:A,MATCH("L",OFFSET('Ride Captains'!A:A,0,MATCH(B2,'Ride Captains'!$1:$1,0)-1),0))

Where Row 1 in Sheet "Ride Captains" contains values 18-01 to 18-100
Where Column B in sheet "Rides" contains values 18-01 to 18-100 (must be identical to values to be matched)
Where Column A in sheet "Rides" contains value to be returned

 

[Trevor_S]

Agreed, my mistake, it is "index" you need, not "indirect".

 

[Marcelo Branco]

Hi Trevor and Yongle

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

M.

 

[Marcelo Branco]

See

http://www.decisionmodels.com/calcsecretsi.htm

M.