Jack
Member
Hi everyone
This one I think is and I have certainly found to be harder than it sounds. I have a range of data spanning 13 columns G6:S30 with each column representing different jobs on any given day in a business for a roster. Each row is a 1/2 hour time slot. So an employee's initials say " FA" can be entered anywhere in this range G6:S30 to give their hours worked and they might start the day in one column and end in another later in the day. I want to pull out their start and end times which appear in F6:F30. So to do this I just need the row for the first occurance of their initials anywhere in that range of 13 columns to use in an index formula to return a value from F for the same row. I got the last instance OK as below but the 1st is tricky because if I replace below MAX with MIN it returns 0. Note the -Row($F6)+1 bit just caibrates to the starting row of 6 used vs the row number of the sheet and works fine. I also tried MINA but this didn't work either.
=INDEX($F6:$S30,SUMPRODUCT(MAX(($G6:$S30=F$35)*ROW($G6:$S30))-ROW($F6)+1),1)
I guess I could concatenate each of the 13 columns in a array entered match formula but that seems tedious and hoping there is a better way as I need to use a few of these formulas.
I have done a lot of googling and it's the fact that I am looking in multiple columns that is causing the problem, single column with multiple criteria is easy but one criteria across a range seems to be tricky.
Any help would really be appreciated.
Cheers
John
This one I think is and I have certainly found to be harder than it sounds. I have a range of data spanning 13 columns G6:S30 with each column representing different jobs on any given day in a business for a roster. Each row is a 1/2 hour time slot. So an employee's initials say " FA" can be entered anywhere in this range G6:S30 to give their hours worked and they might start the day in one column and end in another later in the day. I want to pull out their start and end times which appear in F6:F30. So to do this I just need the row for the first occurance of their initials anywhere in that range of 13 columns to use in an index formula to return a value from F for the same row. I got the last instance OK as below but the 1st is tricky because if I replace below MAX with MIN it returns 0. Note the -Row($F6)+1 bit just caibrates to the starting row of 6 used vs the row number of the sheet and works fine. I also tried MINA but this didn't work either.
=INDEX($F6:$S30,SUMPRODUCT(MAX(($G6:$S30=F$35)*ROW($G6:$S30))-ROW($F6)+1),1)
I guess I could concatenate each of the 13 columns in a array entered match formula but that seems tedious and hoping there is a better way as I need to use a few of these formulas.
I have done a lot of googling and it's the fact that I am looking in multiple columns that is causing the problem, single column with multiple criteria is easy but one criteria across a range seems to be tricky.
Any help would really be appreciated.
Cheers
John