I have one sheet that is tracking PTO where a user has entered the days off. The form allows them to enter a start and end date for their PTO and enters a single record into the sheet.
I have a second sheet that I am looking to pull that data into, but there is only one record per person (column in this sheet where the first sheet is by row). To address this challenge, I have created multiple columns that combine the name of the person and the date (NameDate1, NameDate2, NameDate3). Those NameDateX columns work perfectly getting only data into each of them if the PTO data matches.
Challenge: If I use Index/Match formula and pull in only one column in the Match reference - the result is perfect. However, I want to have the reference to be 14 days long NameDate1 - NameDate14 and when I do that I get NO Error I just get a blank value. I am uncertain why this is the case. Is there something I need to consider?
Here is a picture of me trying to get this to work with a temporary sheet. You can see I pull back an 8 when it is only one column, but nothing when it there is more than one column in my Match range.
Here is the NameDateAll range view:
Here is the NameDate1 range view:
