Index Match with Dynamic Column

bcurtis208

New Member
Joined
Apr 1, 2011
Messages
6
Long time lurker, first time poster.

The formula used below works to get 'Hours Worked' (aka 'Total' on the second tab) for Ident, however, I'm hoping to revise the formula so that instead it bases it on the last column shown on the second table labeled Table 2659. I want to be able to paste new data where the column headers land in different areas in the table/range. Hopefully this makes sense.

Secondly, I'm trying to figure out how to calculate 'Days Worked' based on the number of rows with values in them where they're not blank where the Ident matches.

Current Formula for Hours Worked:
=INDEX(Table2659[Total],MATCH([@Ident],Table2659[Ident],0))

Table1:
IdentHours WorkedDays Worked
1
2
3
4
5

<tbody>
</tbody>











Table2659
IdentName02/01/1902/02/19Total
17.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.0820
27.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.0813
37.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.087.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.0880
47.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.0840
533

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

bcurtis208

New Member
Joined
Apr 1, 2011
Messages
6
Resolved.

Formulas:
=IFERROR(INDEX(HoursWorked!$C$5:$AY$500,MATCH([@Ident],HoursWorked!$C$5:$C$500,0),MATCH("Total",HoursWorked!$C$5:$AY$5,0)),"")

=IF((COUNTA(INDEX(HoursWorked!$C$6:$AY$500,MATCH([@Ident],HoursWorked!$C$6:$C$500,0),0))-2)>0,(COUNTA(INDEX(HoursWorked!$D$6:$AY$500,MATCH([@Ident],HoursWorked!$C$6:$C$500,0),0))-2),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top