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>
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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),"")
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top