VLOOKUP to return column header based on column index value

[gmmark1125]

Here's a fun one! I have a gantt chart production schedule (Table 1) I use to show my team the schedule. Supply Chain also uses this tool to do material planning but it is a LONG process since they have to manually look at the gantt chart to see when jobs start or end. What I'm trying to do is have a table (Table 2) of the jobs populate their start and end dates. I have the start date formula correct but the end date I am struggling with.

We use the production quantity of the job to show when the job will be completed on the gantt chart. I tried to create a type of VLOOKUP that would reference the job quantity to determine the column number of the formula, and ultimately return the column header (date). Any ideas??



Table 1:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]2/19[/TD]
[TD]2/20[/TD]
[TD]2/21[/TD]
[TD]2/22[/TD]
[TD]2/23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]005000[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]005001[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Job[/TD]
[TD]Qty[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]005000[/TD]
[TD]1[/TD]
[TD]2/20[/TD]
[TD]2/23[/TD]
[/TR]
[TR]
[TD]005001[/TD]
[TD]5[/TD]
[TD]2/21[/TD]
[TD]2/22[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

Say your table 1 is located in range A1:E3 ...

and 005000 in Table 2 is located In celle A6 ...

Then in cell C1 you can have : =INDEX($1:$1,MATCH(A6,$2:$2,0))

and in cell D6 ... the array formula : INDEX($1:$1,MATCH(TRUE,ISNUMBER(INDIRECT(ADDRESS(2,MATCH(A6,$2:$2,0)+1,4)):E2),0)+MATCH(A6,$2:$2,0))

HTH