Index/Match to return second and third instance of a matching column header

[novazona]

Preface: MicrosoftProject is not an option, and I am trying to avoid having to manually changecolumn names in the data output file. Thecolumns are not always in the same position in the data dump. Vlookup and Offset using the task numberdoesn’t work because the total number of activities on a given task varies, andtherefore, the next activity/occurrence will be in a different column with eachdownload.
I may be overlooking and/or overthinking something here,but I need some help. I have a dataoutput file from an older web-based tool that repeats column headers similar tothe table below, but with up to one hundred rows and a couple hundred columns. I need a way to return the second, third,fourth, etc. instances of a column header. The row headers are Task 1, Task 2, Task 3, Task 4, etc., and the datafile returns a repeating series of columns for each activity within the task:

<tbody> [TD="width: 24, bgcolor: transparent"] [/TD]
[TD="width: 48, bgcolor: transparent"]

A​

[/TD]
[TD="width: 54, bgcolor: transparent"]

B​

[/TD]
[TD="width: 126, bgcolor: transparent"]

C​

[/TD]
[TD="width: 66, bgcolor: transparent"]

D​

[/TD]
[TD="width: 78, bgcolor: transparent"]

E​

[/TD]
[TD="width: 122, bgcolor: transparent"]

F​

[/TD]
[TD="width: 52, bgcolor: transparent"]

G​

[/TD]
[TD="width: 84, bgcolor: transparent"]

H​

[/TD]
[TD="width: 102, bgcolor: transparent"]

I​

[/TD]
[TD="width: 84, bgcolor: transparent"]

J​

[/TD]
[TD="width: 108, bgcolor: transparent"]

K​

[/TD]
[TD="width: 24, bgcolor: transparent"] 1 [/TD]
[TD="width: 48, bgcolor: transparent"] Task [/TD]
[TD="width: 54, bgcolor: transparent"] Activity No (index: 1) [/TD]
[TD="width: 126, bgcolor: transparent"] Activity Name [/TD]
[TD="width: 66, bgcolor: transparent"] Estimated Complete Date [/TD]
[TD="width: 78, bgcolor: transparent"] Actual Complete Date [/TD]
[TD="width: 122, bgcolor: transparent"] Status [/TD]
[TD="width: 52, bgcolor: transparent"] Activity No (index: 2) [/TD]
[TD="width: 84, bgcolor: transparent"] Activity Name [/TD]
[TD="width: 102, bgcolor: transparent"] Estimated Complete Date [/TD]
[TD="width: 84, bgcolor: transparent"] Actual Complete Date [/TD]
[TD="width: 108, bgcolor: transparent"] Status [/TD]
[TD="width: 24, bgcolor: transparent"] 2 [/TD]
[TD="width: 48, bgcolor: transparent"] Task 1 [/TD]
[TD="width: 54, bgcolor: transparent"] 1 [/TD]
[TD="width: 126, bgcolor: transparent"] Backup old computer [/TD]
[TD="width: 66, bgcolor: transparent"] 09/30/17 [/TD]
[TD="width: 78, bgcolor: transparent"] 10/01/18 [/TD]
[TD="width: 122, bgcolor: transparent"] Delayed due to lack of resources [/TD]
[TD="width: 52, bgcolor: transparent"] 2 [/TD]
[TD="width: 84, bgcolor: transparent"] Install new software [/TD]
[TD="width: 102, bgcolor: transparent"] 10/15/15 [/TD]
[TD="width: 84, bgcolor: transparent"] 10/15/15 [/TD]
[TD="width: 108, bgcolor: transparent"] Completed on time [/TD]
[TD="width: 24, bgcolor: transparent"] 3 [/TD]
[TD="width: 48, bgcolor: transparent"] Task 2 [/TD]
[TD="width: 54, bgcolor: transparent"] 1 [/TD]
[TD="width: 126, bgcolor: transparent"] Submit purchase order for approval [/TD]
[TD="width: 66, bgcolor: transparent"] 01/01/17 [/TD]
[TD="width: 78, bgcolor: transparent"] 01/01/17 [/TD]
[TD="width: 122, bgcolor: transparent"] Submitted on time [/TD]
[TD="width: 52, bgcolor: transparent"] 2 [/TD]
[TD="width: 84, bgcolor: transparent"] Order desks [/TD]
[TD="width: 102, bgcolor: transparent"] 01/02/17 [/TD]
[TD="width: 84, bgcolor: transparent"] 01/09/17 [/TD]
[TD="width: 108, bgcolor: transparent"] Delayed due to being out of office [/TD]
[TD="width: 24, bgcolor: transparent"] 4 [/TD]
[TD="width: 48, bgcolor: transparent"] Task 3 [/TD]
[TD="width: 54, bgcolor: transparent"] 1 [/TD]
[TD="width: 126, bgcolor: transparent"] Test work station [/TD]
[TD="width: 66, bgcolor: transparent"] 04/04/17 [/TD]
[TD="width: 78, bgcolor: transparent"] 03/01/17 [/TD]
[TD="width: 122, bgcolor: transparent"] Able to complete early due to other projects being on hold [/TD]
[TD="width: 52, bgcolor: transparent"] 2 [/TD]
[TD="width: 84, bgcolor: transparent"] Write test station report [/TD]
[TD="width: 102, bgcolor: transparent"] 04/11/17 [/TD]
[TD="width: 84, bgcolor: transparent"] 03/15/17 [/TD]
[TD="width: 108, bgcolor: transparent"] Able to write report early due to other projects being on hold [/TD]
</tbody>

What I need is a way to quickly status a given task andreview all activities and their estimated completion date, actual completiondate, and status. My first thought waspivot table where the user could filter on the task. I need something like this:
[TABLE="width: 603"]
<tbody>[TR]
[TD="width: 90"] Task (Filter):
[/TD]
[TD="width: 168"] Task 1
[/TD]
[TD="width: 168, bgcolor: transparent"][/TD]
[TD="width: 156, bgcolor: transparent"][/TD]
[TD="width: 222, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 90, bgcolor: transparent"]
[/TD]
[TD="width: 168, bgcolor: transparent"]
[/TD]
[TD="width: 168, bgcolor: transparent"][/TD]
[TD="width: 156, bgcolor: transparent"][/TD]
[TD="width: 222, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 90"] Activity No
[/TD]
[TD="width: 168"] Activity Name
[/TD]
[TD="width: 168"] Estimated Complete Date
[/TD]
[TD="width: 156"] Actual Complete Date
[/TD]
[TD="width: 222"] Status
[/TD]
[/TR]
[TR]
[TD="width: 90, bgcolor: transparent"]

1​

[/TD]
[TD="width: 168, bgcolor: transparent"] Backup old computer
[/TD]
[TD="width: 168, bgcolor: transparent"]

9/30/2017​

[/TD]
[TD="width: 156, bgcolor: transparent"]

10/1/2018​

[/TD]
[TD="width: 222, bgcolor: transparent"] Delayed due to lack of resources
[/TD]
[/TR]
[TR]
[TD="width: 90, bgcolor: transparent"]

2​

[/TD]
[TD="width: 168, bgcolor: transparent"] Install new software
[/TD]
[TD="width: 168, bgcolor: transparent"]

10/15/2015​

[/TD]
[TD="width: 156, bgcolor: transparent"]

10/15/2015​

[/TD]
[TD="width: 222, bgcolor: transparent"] Completed on time
[/TD]
[/TR]
</tbody>[/TABLE]

Because each “Activity no” column has its own indexnumber designation in the title, but the subsequent fields do not, I’m not ableto compile the data in clean pivot table with the activity on the left and thedates and status in columns. My solutionwas to pivot based on row headers in compact format, tweak to formatting, andthen do index/match to pull the estimated completion date.

Because he columns are not always in the same positionbased on the data dump, the index/match function works well. In cell C11, I have used:
=INDEX($A$2:$K$4,MATCH($C$8,$A$2:$A$4,0),MATCH(C10,$A$1:$K$1,0))
What I need help with is the yellow cells below. How can I get Excel to look at the data dumpand either pull the next instance of Estimated Completion Date to cell C12, ormatch both the task number and the row label and return the estimatedcompletion date? The total number ofactivities on a given task varies, and therefore, the next occurrence will bein a different column for each task. However, the activity number, activity name,estimated completion date, actual completion date and status always repeatorder. Is there a way to somehow embedand offset based on activity number within the index match function?


[TABLE="width: 604"]
<tbody>[TR]
[TD="width: 139, bgcolor: transparent"][/TD]
[TD="width: 139, bgcolor: transparent"]

B​

[/TD]
[TD="width: 175, bgcolor: transparent"]

C​

[/TD]
[TD="width: 152, bgcolor: transparent"]

D​

[/TD]
[TD="width: 201, bgcolor: transparent"]

E​

[/TD]
[/TR]
[TR]
[TD="width: 139, bgcolor: transparent"][/TD]
[TD="width: 139"] Task
[/TD]
[TD="width: 175"] Task 1
[/TD]
[TD="width: 152, bgcolor: transparent"][/TD]
[TD="width: 201, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 139, bgcolor: transparent"][/TD]
[TD="width: 139, bgcolor: transparent"][/TD]
[TD="width: 175, bgcolor: transparent"][/TD]
[TD="width: 152, bgcolor: transparent"][/TD]
[TD="width: 201, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 139, bgcolor: transparent"]

10​

[/TD]
[TD="width: 139"] Row Labels
[/TD]
[TD="width: 175"] Estimated Completion Date
[/TD]
[TD="width: 152"] Actual Complete Date
[/TD]
[TD="width: 201"] Comment
[/TD]
[/TR]
[TR]
[TD="width: 139, bgcolor: transparent"]

11​

[/TD]
[TD="width: 139, bgcolor: transparent"] Backup old computer
[/TD]
[TD="width: 175, bgcolor: transparent"]

9/30/2017​

[/TD]
[TD="width: 152, bgcolor: transparent"]

10/1/2018​

[/TD]
[TD="width: 201, bgcolor: transparent"] Delayed due to lack of resources
[/TD]
[/TR]
[TR]
[TD="width: 139, bgcolor: transparent"]

12​

[/TD]
[TD="width: 139, bgcolor: transparent"] Install new software
[/TD]
[TD="width: 175"]
[/TD]
[TD="width: 152"]
[/TD]
[TD="width: 201"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

If ive read correctly:

=INDEX(INDEX($A$2:$AA$4,MATCH($B$7,$A$2:$A$4,0),0),SMALL(IF($A$1:$AA$1=B$9,COLUMN($A$1:$AA$1)),$A10))

entered CTRL-SHIFT-ENTER

 

[novazona]

[TABLE="width: 500"]
<tbody>[TR]
[TD]

[TABLE="width: 500"]
<tbody>[TR]
[TD]
Thank you for the reply, I’m very grateful for your time! I pasted via Microsoft word, and it appears that the tables shifted because the formula you provided references blank cells. I tried manually creating tables here and labeling the rows and columns a little better. I’m trying to create the table manually to see if that works:



Data Source:
[TABLE="width: 1221"]
<tbody>[TR]
[TD="width: 14, bgcolor: transparent"][/TD]
[TD="width: 46, bgcolor: transparent, align: center"]A

[/TD]
[TD="width: 138, bgcolor: transparent, align: center"]B
[/TD]
[TD="width: 174, bgcolor: transparent, align: center"]C
[/TD]
[TD="width: 152, bgcolor: transparent, align: center"]D
[/TD]
[TD="width: 201, bgcolor: transparent, align: center"]E
[/TD]
[TD="width: 183, bgcolor: transparent, align: center"]F
[/TD]
[TD="width: 76, bgcolor: transparent, align: center"]G
[/TD]
[TD="width: 133, bgcolor: transparent, align: center"]H
[/TD]
[TD="width: 159, bgcolor: transparent, align: center"]I
[/TD]
[TD="width: 159, bgcolor: transparent"]J
[/TD]
[TD="width: 192, bgcolor: transparent"]K
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]2
[/TD]
[TD="width: 46, bgcolor: #E7E6E6"]Task

[/TD]
[TD="width: 138, bgcolor: #E7E6E6"]Activity No (Index 1)
[/TD]
[TD="width: 174, bgcolor: #E7E6E6"]Activity Name

[/TD]
[TD="width: 152, bgcolor: #E7E6E6"]Estimated Completion Date
[/TD]
[TD="width: 201, bgcolor: #E7E6E6"]Actual Complete Date
[/TD]
[TD="width: 183, bgcolor: #E7E6E6"]Comment
[/TD]
[TD="width: 76, bgcolor: #E7E6E6"]Activity No (Index 2)
[/TD]
[TD="width: 133, bgcolor: #E7E6E6"]Activity Name
[/TD]
[TD="width: 159, bgcolor: #E7E6E6"]Estimated Completion Date
[/TD]
[TD="width: 159, bgcolor: #E7E6E6"]Actual Complete
[/TD]
[TD="width: 192, bgcolor: #E7E6E6"]Comment
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]3
[/TD]
[TD="width: 46, bgcolor: #E7E6E6"]Task 1

[/TD]
[TD="width: 138, bgcolor: #E7E6E6, align: right"]

1
​

[/TD]
[TD="width: 174, bgcolor: #E7E6E6"]Backup old computer
[/TD]
[TD="width: 152, bgcolor: #E7E6E6, align: right"]9/30/2017
[/TD]
[TD="width: 201, bgcolor: #E7E6E6, align: right"]10/1/2018
[/TD]
[TD="width: 183, bgcolor: #E7E6E6"]Delayed due to lack of resources
[/TD]
[TD="width: 76, bgcolor: #E7E6E6, align: right"]

2
​

[/TD]
[TD="width: 133, bgcolor: #E7E6E6"]Install new software
[/TD]
[TD="width: 159, bgcolor: #E7E6E6, align: right"]10/15/2015
[/TD]
[TD="width: 159, bgcolor: #E7E6E6, align: right"]10/15/2015
[/TD]
[TD="width: 192, bgcolor: #E7E6E6"]Completed on time
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]4
[/TD]
[TD="width: 46, bgcolor: #E7E6E6"]Task 2

[/TD]
[TD="width: 138, bgcolor: #E7E6E6, align: right"]

1
​

[/TD]
[TD="width: 174, bgcolor: #E7E6E6"]Submit purchase order for approval
[/TD]
[TD="width: 152, bgcolor: #E7E6E6, align: right"]1/1/2017
[/TD]
[TD="width: 201, bgcolor: #E7E6E6, align: right"]1/1/2017
[/TD]
[TD="width: 183, bgcolor: #E7E6E6"]Submitted on time
[/TD]
[TD="width: 76, bgcolor: #E7E6E6, align: right"]

2
​

[/TD]
[TD="width: 133, bgcolor: #E7E6E6"]Order desks
[/TD]
[TD="width: 159, bgcolor: #E7E6E6, align: right"]1/2/2017
[/TD]
[TD="width: 159, bgcolor: #E7E6E6, align: right"]1/9/2017
[/TD]
[TD="width: 192, bgcolor: #E7E6E6"]Delayed due to being out of office
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]5
[/TD]
[TD="width: 46, bgcolor: #E7E6E6"]Task 3
[/TD]
[TD="width: 138, bgcolor: #E7E6E6, align: right"]

1
​

[/TD]
[TD="width: 174, bgcolor: #E7E6E6"]Test work station
[/TD]
[TD="width: 152, bgcolor: #E7E6E6, align: right"]4/4/2017
[/TD]
[TD="width: 201, bgcolor: #E7E6E6, align: right"]3/1/2017
[/TD]
[TD="width: 183, bgcolor: #E7E6E6"]Able to complete early due to other projects being on hold
[/TD]
[TD="width: 76, bgcolor: #E7E6E6, align: right"]

2
​

[/TD]
[TD="width: 133, bgcolor: #E7E6E6"]Write test station report
[/TD]
[TD="width: 159, bgcolor: #E7E6E6, align: right"]4/11/2017
[/TD]
[TD="width: 159, bgcolor: #E7E6E6, align: right"]3/15/2017
[/TD]
[TD="width: 192, bgcolor: #E7E6E6"]Able to write report early due to other projects being on hold
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Need to populate C13:E13
[TABLE="width: 534"]
<tbody>[TR]
[TD="width: 46, bgcolor: transparent"][/TD]
[TD="width: 138, bgcolor: transparent, align: center"]B
[/TD]
[TD="width: 174, bgcolor: transparent, align: center"]C
[/TD]
[TD="width: 152, bgcolor: transparent, align: center"]D
[/TD]
[TD="width: 201, bgcolor: transparent, align: center"]E
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]9
[/TD]
[TD]Task:
[/TD]
[TD]Task 1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]10
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]11
[/TD]
[TD]Row Labels
[/TD]
[TD="bgcolor: #DDEBF7"]Estimated Completion Date
[/TD]
[TD="bgcolor: #DDEBF7"]Actual Complete Date
[/TD]
[TD="bgcolor: #DDEBF7"]Comment
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]12
[/TD]
[TD="bgcolor: transparent"]Backup old computer
[/TD]
[TD="bgcolor: #FCE4D6, align: right"]9/30/2017
[/TD]
[TD="bgcolor: #FCE4D6, align: right"]10/1/2018
[/TD]
[TD="bgcolor: #FCE4D6"]Delayed due to lack of resources
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]13
[/TD]
[TD="bgcolor: transparent"]Install new software
[/TD]
[TD="width: 174, bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I adjusted your formula and put in cell C13: =INDEX(INDEX($A$2:$AA$4,MATCH($C$9,$A$2:$A$4,0),0),SMALL(IF($A$1:$AA$1=B$13,COLUMN($A$1:$AA$1)),$B12))entered with control + shift + enter, but no luck.

If ive read correctly:

=INDEX(INDEX($A$2:$AA$4,MATCH($B$7,$A$2:$A$4,0),0),SMALL(IF($A$1:$AA$1=B$9,COLUMN($A$1:$AA$1)),$A10))

entered CTRL-SHIFT-ENTER