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:

2/19	2/20	2/21	2/22	2/23
	005000			1
		005001	5

<tbody>
</tbody>

Table 2:

Job	Qty	Start	End
005000	1	2/20	2/23
005001	5	2/21	2/22

<tbody>
</tbody>

 

[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