Firstly, I hastely put up the formula before making minor tweaks.... so here is the cleaner version:
Code:
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX('leave types'!$B$3:$B$8,LOOKUP(9.9999E+307,(MATCH(File!$A$4:$A$500,$C$4&$B10&'leave types'!$B$3:$B$8,0))))))
The inner most MATCH() function takes advantage of your concatenated values in the File sheet and matches each of thosesvalue in File!$A$4:$A$500 to each value in the combination of $C$4&$B10&'leave types'!$B$3:$B$8 and when copied down the B10 value changes (i.e date only changes).
The result of the MATCH() function is an array of #N/A values where matches were not found and possibly some numeric results signifying a match was found and identifying in which position of the 'leave types'!$B$3:$B$8 range the match(es) were found.
LOOKUP(9.9999E+307,MATCH()) is then used to identify what the last numeric value, if any, is. Lookup() looks for the last value in a range that is less than or equal to the lookup value (in this case the largest number Excel recognizes, 9.9999E+307)... Lookup also ignores errors like #N/A. So, basically this Lookup should find the number that matches the position in 'leave types'!$B$3:$B$8 (if more than one exists, it will take the last).. and then Indexes it in that same range to return the actual text string at that position.
The other LOOKUP... LOOKUP(REPT("z",255),CHOOSE({1,2},"", is used as an error trap.... working in a similar fashion, this lookup looks for the last text string in range {"",result of the INDEX() function} and returns the last that is smaller than "z" repeated 255 times.... so if the INDEX() doesn't find a match and returns a #N/A error, then Lookup ignores the error and returns a blank ("")... otherwise, if a text string was returned, then that result is returned.
Incidentally, if you have Excel 2007 or 2010, you can use IFERROR instead:
Code:
=IFERROR(INDEX('leave types'!$B$3:$B$8,LOOKUP(9.9999E+307,(MATCH(File!$A$4:$A$500,$C$4&$B10&'leave types'!$B$3:$B$8,0)))),"")
Hope that helps a bit.
Note: You can use the Formula evaluator tool (in 2003, in Tools|Formula Auditing|Evaluate Formula. In 2007+, in Formulas tab, Formula Auding section, Evaluate formula). Please reduce the $A$4:$A$500 range first to about $A$4:$A$20 and use the 456665 ID so that it is easier to follow the logic.