Help in Formula

[petitallien]

Good evening,

I work in scheduling. I spend a fair amount of time converting a schedule every day. I am sure I have a pic-nic (problem in chair, not in Computer) moment. I am confident to excel can help.

Let me explain:

I have a schedule organized by date(Column A), with Crew associated to ships Column B to Column E (there are many more in the full sheet), and I need to get the table on the right where the same information is organized differently. I struggle to find the formulas for Columns M and Column N. I would also much prefer to stay with Formulas as the file may not be by computer savvy operators, and I am not good enough to understand it.

Any Bright genius who would take the challenge?

 

[awoohaw]

Hi there. it would be great if you used the xl2bb add in and posted a mini workbook with sample data (as well as the lookup source). It is difficult and time consuming for people that want to help to have to reconstruct your model. The link is below. I am certain you will get a faster response when you can do this.

 

[Legacy 143009]

For the given picture this should work.
M9:

=INDEX($A$5:$A$24,MATCH($L11,INDEX($B$5:$E$24,0,MATCH($K11,$B$5:$E$5,0)),0))

N9:

=INDEX($A$5:$A$24,MATCH($L11,INDEX($B$5:$E$24,0,MATCH($K11,$B$5:$E$5,0)),1)+1)

 

[petitallien]

For the given picture this should work.
M9:

=INDEX($A$5:$A$24,MATCH($L11,INDEX($B$5:$E$24,0,MATCH($K11,$B$5:$E$5,0)),0))

N9:

=INDEX($A$5:$A$24,MATCH($L11,INDEX($B$5:$E$24,0,MATCH($K11,$B$5:$E$5,0)),1)+1)

View attachment 85339

Thank you, let me try it !

 

[petitallien]

For the given picture this should work.
M9:

=INDEX($A$5:$A$24,MATCH($L11,INDEX($B$5:$E$24,0,MATCH($K11,$B$5:$E$5,0)),0))

N9:

=INDEX($A$5:$A$24,MATCH($L11,INDEX($B$5:$E$24,0,MATCH($K11,$B$5:$E$5,0)),1)+1)

View attachment 85339

T

Hi there. it would be great if you used the xl2bb add in and posted a mini workbook with sample data (as well as the lookup source). It is difficult and time consuming for people that want to help to have to reconstruct your model. The link is below. I am certain you will get a faster response when you can do this.

True - I tried but the security system did not allow to install it

hank you, let me try it !

 

[awoohaw]

T

True - I tried but the security system did not allow to install it

hank you, let me try it !

that is understandable. you can only do what you can. But, it seems you have a solution pending!