Get Column Header from Match in a Array?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mark.wolven@gmail.com - 11 Mar 2008 21:39 GMT

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.

Reply to this Message

T. Valko - 11 Mar 2008 23:12 GMT

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))

Signature

Biff
Microsoft Excel MVP

> Here's what I am trying to do.
>
[quoted text clipped - 5 lines]
> me what column it is in - or give me the text value from row 1 in that
> column.

Reply to this Message

mark.wolven@gmail.com - 12 Mar 2008 14:47 GMT

> Dates on sheet2 in A1:I1
>
[quoted text clipped - 19 lines]
> > me what column it is in - or give me the text value from row 1 in that
> > column.

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

Reply to this Message

Pete_UK - 12 Mar 2008 14:54 GMT

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

On Mar 12, 1:47 pm, mark.wol...@gmail.com wrote:

> > Dates on sheet2 in A1:I1
>
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Reply to this Message

mark.wolven@gmail.com - 12 Mar 2008 15:24 GMT

> 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
[quoted text clipped - 33 lines]
>
> > - Show quoted text -

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?

Reply to this Message

T. Valko - 12 Mar 2008 18:31 GMT

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)

Signature

Biff
Microsoft Excel MVP

>> 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
[quoted text clipped - 49 lines]
>
> Which of course, doesn't work. Thoughts?

Reply to this Message