# Index Match with Dynamic Column

#### bcurtis208

##### New Member
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:
 Ident Hours Worked Days Worked 1 2 3 4 5

<tbody>
</tbody>

Table2659
 Ident Name 02/01/19 02/02/19 Total 1 7.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.08 20 2 7.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.08 13 3 7.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.08 7.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.08 80 4 7.48 - 11.3911.39 - 11.4211.93 - 14.5414.55 - 16.08 40 5 33

<tbody>
</tbody>

### 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
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),"")

Replies
6
Views
31
Replies
14
Views
89
Replies
2
Views
76
Replies
2
Views
56
Replies
5
Views
43