Get Column Header from Match in a Array?

  • Thread starter Thread starter mark.wolven
  • Start date Start date
M

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.
 
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))
 
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))
Click to expand...

For Match to work, does the array need to be in order?
 
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
 
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
Click to expand...

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?
 
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)
 
Back
Top