Dear Ninjas,
I have 2 tables of students' visit information (namely Table1 and Table2).
In the first one (Table 1) I have Student ID, Visit number and Visit date (From A1 to C7).
In the second one I have Student ID, Submission date and Visit date (From H1 to J2).
I want to populate a value in ‘Visit date’ which should be the date immediately prior to ‘Submission date’ from the Table 1.
I have entered 21-Jun-16 in ‘Submission date’ and I want to return 12-Jun-16 in Visit date in Table 2
I tried the following array formula, by modifying the formula which I use to return the next bigger value.
{=INDEX(C2:C7,MATCH(TRUE,IF(H2=$A$2:$A$7,INDEX($C$2:$C$7<I2,0)),0))}
But this did not work. Can anyone help please?
I have 2 tables of students' visit information (namely Table1 and Table2).
In the first one (Table 1) I have Student ID, Visit number and Visit date (From A1 to C7).
In the second one I have Student ID, Submission date and Visit date (From H1 to J2).
I want to populate a value in ‘Visit date’ which should be the date immediately prior to ‘Submission date’ from the Table 1.
I have entered 21-Jun-16 in ‘Submission date’ and I want to return 12-Jun-16 in Visit date in Table 2
I tried the following array formula, by modifying the formula which I use to return the next bigger value.
{=INDEX(C2:C7,MATCH(TRUE,IF(H2=$A$2:$A$7,INDEX($C$2:$C$7<I2,0)),0))}
But this did not work. Can anyone help please?
