Hopefully not too difficult ?!?!
The question here is E7, which is a payroll number, this can appear multiple times, so in the formula below, which will go from G5 across to M5, if it picks up the same payroll number it needs to pick up the second value, instead of the first
To explain Further this is the source data
Because the value 3803 (column B) appears twice, my VLOOKUP won't work. How can I pick up both?
IF(AND(R$4>=VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,6,0),R$4<=VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,7,0)),VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,3,0),BL7))
The question here is E7, which is a payroll number, this can appear multiple times, so in the formula below, which will go from G5 across to M5, if it picks up the same payroll number it needs to pick up the second value, instead of the first
To explain Further this is the source data
LeaveTemplate.xls | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
1 | 3803 | XLE | DHM | 03/01/2010 | 03/01/2010 | ||||
2 | 4006 | XDE | FL | 04/11/2009 | 13/11/2009 | ||||
3 | 3803 | XLE | LMM | 07/01/2010 | 08/01/2010 | ||||
4 | 5034 | XLE | PHM | 06/11/2009 | 15/11/2009 | ||||
LeaveTemplate |
Because the value 3803 (column B) appears twice, my VLOOKUP won't work. How can I pick up both?
IF(AND(R$4>=VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,6,0),R$4<=VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,7,0)),VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,3,0),BL7))