Excel - Lookup date in matrix and return column heading

wmont101 0
Viewed 33

I have a matrix between Products and Enablers, where the intersection between the two represents a point in time.

Product list Enabler 1 Enabler 2 Enabler 3
Product 1 10-Oct 11-Oct 20-Oct
Product 2 20-Nov 25-Nov 01-Dec
Product 3 10-Oct 21-Oct 25-Oct

I need to turn this into a 'timeline' view so visually there are two ways to see the data, where the dates are across the top and based on the timing in the first table, it returns the corresponding 'Enabler' at the correct date...something like

Product list 10-Oct 11-Oct 12-Oct
Product 1 Enabler 1 Enabler 2
Product 2
Product 3 Enabler 1

Does anyone have any ideas how I'd do this? I think it requires an INDEX MATCH array formula as it needs to look across the matrix to find the date in that row, then return what is in the header column...but this isn't my area of expertise and I just can't seem to figure out how to make it work.

excel-formula excel
1 Answers

One approach might be to return this as an array. You could do:

=IF( ( Table1[[Enabler 1]:[Enabler 3]] = B7:D7 ) * ( Table1[Product list] = A8:A10),
     Table1[[#Headers],[Enabler 1]:[Enabler 3]],
     "" )

where Table1 is an Excel Table that holds your Product List and Enablers as columns (as shown in your first table); A8:A10 is the list of products in your second table; and B7:D7 is the list of dates in your second table shown as column headers. The formula would be placed in the upper left cell of your second table - in my example, B8 as shown here:

enter image description here

The result will spill into the second table.

If you wanted your second table to be an Excel Table, the approach would be different as arrays cannot spill into Excel Tables.

mark fitzpatrick0
Related