Excel Find this Value and this Value and where they meet enter the new value

Hi Guys I know I should know this but I am haveing a brain freeze!

So in sheet1 I have a list of people and each coloumn = a date

Emp No. Rota 01/04/2018 02/04/2018 03/04/2018 04/04/2018 05/04/2018 06/04/2018 07/04/2018 08/04/2018 09/04/2018 10/04/2018 11/04/2018 12/04/2018 13/04/2018 14/04/2018 15/04/2018 16/04/2018 17/04/2018 18/04/2018 19/04/2018 20/04/2018 21/04/2018 22/04/2018
10087248 1
10111378 1
10104720 1
10103818 1
10128761 1
10109686 1
10110853 1
10123778 1
10105003 1
10115410 1
10109674 1
10117543 1
10114185 1
10105990 1
10114457 1
10087185 1
10121055 1


in sheet 2 I have a list of dates and then each coloumn = a team

Date 1 2 3 4 5 7A 7B R1 E1
Mon 01/01/2018 06:00 14:00 14:00 06:00 14:00 18:00 18:00 08:00 14:00
Tue 02/01/2018 06:00 14:00 14:00 06:00 14:00 18:00 18:00 08:00 14:00
Wed 03/01/2018 14:00 14:00 06:00 14:00 18:00 18:00 08:00 14:00
Thu 04/01/2018 06:00 14:00 14:00 06:00 14:00 18:00 18:00 08:00 14:00
Fri 05/01/2018 06:00 14:00 06:00 14:00 14:00 08:00 14:00
Sat 06/01/2018
Sun 07/01/2018 14:00 06:00 18:00
Mon 08/01/2018 14:00 06:00 06:00 14:00 14:00 18:00 18:00 08:00 08:00
Tue 09/01/2018 14:00 06:00 06:00 14:00 14:00 18:00 18:00 08:00 08:00
Wed 10/01/2018 14:00 06:00 14:00 14:00 18:00 18:00 08:00 08:00
Thu 11/01/2018 14:00 06:00 06:00 14:00 14:00 18:00 18:00 08:00 08:00
Fri 12/01/2018 06:00 06:00 14:00 14:00 14:00 08:00 08:00
Sat 13/01/2018
Sun 14/01/2018 14:00 06:00 18:00
Mon 15/01/2018 06:00 14:00 14:00 06:00 14:00 18:00 18:00 08:00 14:00
Tue 16/01/2018 06:00 14:00 14:00 06:00 14:00 18:00 18:00 08:00 14:00


I want to be able to see when the person is due to start in sheet 1.

I am trying to use index match but cant get it to work.

2 Answers
2

You need some common denominator in your data sheet.
That means, in both Table1 and Table2 you need a column to contain the same data which will act as your criteria range.

Then it appears you need to use =VLOOKUP()

=VLOOKUP()

Can't really help you further without you editing either your Sheet1 or Sheet2 to have at least 1 column in common,
As of now, you have no way of matching something to a specific number given there are no possible matches (unless I'm missunderstanding what you're trying to achieve)

Ok So I cheated I made the dates match so both start from 01/03/2018 then I fliped the Rota so both tables had the date across the top and then just used =VLOOKUP($B2,Sheet3!$A:$QN,COUNT($C$1:C$1,FALSE))

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.