Get Column Header from Match in a Array?

TheMSsForum.com: The Microsoft Software Forum

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.
Top

Re: Get Column Header from Match in a Array? by T

T
Tue Mar 11 17:12:18 CDT 2008

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


--
Biff
Microsoft Excel MVP


<mark.wolven@gmail.com> wrote in message
news:0cb48bf9-aced-40a2-ad74-07c272906c06@i29g2000prf.googlegroups.com...
> 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.


Top

Re: Get Column Header from Match in a Array? by mark

mark
Wed Mar 12 08:47:54 CDT 2008

On Mar 11, 6:12 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> 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))
>
> --
> Biff
> Microsoft Excel MVP
>
> <mark.wol...@gmail.com> wrote in message
>
> news:0cb48bf9-aced-40a2-ad74-07c272906c06@i29g2000prf.googlegroups.com...
>
> > 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.

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

Re: Get Column Header from Match in a Array? by Pete_UK

Pete_UK
Wed Mar 12 08:54:50 CDT 2008

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=A0pm, mark.wol...@gmail.com wrote:
> On Mar 11, 6:12 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>
>
>
>
>
> > Dates on sheet2 in A1:I1
>
> > Enter this formula in sheet2 B1 and copy across as needed:
>
> > =3DINDEX(Sheet1!$B1:$J1,MATCH(A1,Sheet1!$B2:$J2,0))
>
> > --
> > Biff
> > Microsoft Excel MVP
>
> > <mark.wol...@gmail.com> wrote in message
>
> >news:0cb48bf9-aced-40a2-ad74-07c272906c06@i29g2000prf.googlegroups.com...=

>
> > > 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.
>
> For Match to work, does the array need to be in order?- Hide quoted text -=

>
> - Show quoted text -

Top

Re: Get Column Header from Match in a Array? by mark

mark
Wed Mar 12 09:24:17 CDT 2008

On Mar 12, 9:54 am, Pete_UK <pashu...@auditel.net> wrote:
> 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:
>
> > On Mar 11, 6:12 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>
> > > 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))
>
> > > --
> > > Biff
> > > Microsoft Excel MVP
>
> > > <mark.wol...@gmail.com> wrote in message
>
> > >news:0cb48bf9-aced-40a2-ad74-07c272906c06@i29g2000prf.googlegroups.com...
>
> > > > 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.
>
> > For Match to work, does the array need to be in order?- Hide quoted text -
>
> > - 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?
Top

Re: Get Column Header from Match in a Array? by T

T
Wed Mar 12 12:31:10 CDT 2008

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)


--
Biff
Microsoft Excel MVP


<mark.wolven@gmail.com> wrote in message
news:c58725e3-609f-4f02-ae07-2a1f1d85bfe8@q78g2000hsh.googlegroups.com...
> On Mar 12, 9:54 am, Pete_UK <pashu...@auditel.net> wrote:
>> 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:
>>
>> > On Mar 11, 6:12 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>>
>> > > 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))
>>
>> > > --
>> > > Biff
>> > > Microsoft Excel MVP
>>
>> > > <mark.wol...@gmail.com> wrote in message
>>
>> > >news:0cb48bf9-aced-40a2-ad74-07c272906c06@i29g2000prf.googlegroups.com...
>>
>> > > > 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.
>>
>> > For Match to work, does the array need to be in order?- Hide quoted
>> > text -
>>
>> > - 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?


Top