Multiple Formula Problem (vlookup, index match, if, and...)

Hi All,

Hoping for some fairly large scale advice/help here.

I have a table of info as attached in Sheet 1. Which basically shows a time slot, a name and an Y or N as in whether the name is available in a certain timeslot.

I need to transfer that info into the table in Sheet 2 which has a few additional details too. The first thing I need doing is a name to be populated in the table in Sheet 2 next to a slot which the table in sheet 1 has a Y in (hopefully this becomes clearer when you view the tables!!)

I've been experimenting with vlookups and others but can't quite get anything to work that well.

My next issue is looking at the rows in the table in Sheet 2. Where the Number in the students column exceeds a certain number ie. 50 I'd need an extra name to appear in that row, I'm thinking of some kind of conditional formatting but not sure where to start - this one is more of a bonus if I can get that working though.

Apologies if that's not clear, please let me know if I can help clarify anything!

Thanks

Try this array formula

Formula:

Please Login or Register  to view this content.

I send the file with this approach

Best regards

That's great thank you.

One further question - is it possible for the lookup to be done using the text in each table eg. "Tue AM" rather than by row?

Thanks,

Jd

I suppose the improvements included in the file give the request.


If you like mark as solved and add reputation.
Best regards