Get Column Header from Match in a Array?

[mark.wolven]

Here's what I am trying to do.

I have a table of date, with the column names in Row 1, Column B-J.

The Data from B2:J11 is dates. On a second sheet, I have the dates
listed chronologically, I'd like to use a formula to take at the date
in the second sheet, and look at the array on the first sheet and tell
me what column it is in - or give me the text value from row 1 in that
column.

 

[T. Valko]

Dates on sheet2 in A1:I1

Enter this formula in sheet2 B1 and copy across as needed:

=INDEX(Sheet1!$B1:$J1,MATCH(A1,Sheet1!$B2:$J2,0))

 

[mark.wolven]

Dates on sheet2 in A1:I1

Enter this formula in sheet2 B1 and copy across as needed:

=INDEX(Sheet1!$B1:$J1,MATCH(A1,Sheet1!$B2:$J2,0))

For Match to work, does the array need to be in order?

 

[Pete_UK]

In this case Biff has used 0 as the third parameter in the MATCH
function, so the array does not need to be sorted as it is looking for
an exact match.

Hope this helps.

Pete

 

[mark.wolven]

In this case Biff has used 0 as the third parameter in the MATCH
function, so the array does not need to be sorted as it is looking for
an exact match.

Hope this helps.

Pete

OK, I have it working, but not fully.

If I use this formula: =INDEX('2008'!$B$1:$J$1,MATCH(A4,'2008'!$B$2:$J
$2,0)) it works.

But, my array is more than one row high, I need to do something like
this:

=INDEX('2008'!$B$1:$J$1,MATCH(A4,'2008'!$B$2:$J$11,0))

Which of course, doesn't work. Thoughts?

 

[T. Valko]

Try this array formula** :

=INDEX(B1:J1,MATCH(MIN(IF(B2:J11=A4,COLUMN(B1:J1)-1)),COLUMN(B1:J1)-1,0))

If there are multiple matches of A4 within B2:J11 the formula will return
the header that corresponds to the *first* match from left to right.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)