If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 nth occurrence of MATCH
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

nth occurrence of MATCH

#11
February 13th 09, 03:07 PM posted to microsoft.public.excel.worksheet.functions
 Chevron7 external usenet poster Posts: 4
nth occurrence of MATCH

How can I use this formula on row 14 in another worksheet?

"Glenn" wrote:

> Paul D. Simon wrote:
> > An Excel file that is now being sent to me each month contains a large
> > database table containing several columns. Reports I generate from
> > this file rely on Vlookup formulas. However, each month, the position
> > of several columns changes due to additions and/or deletions of
> > columns within the database from the previous month's file. So
> > copying in my formulas each month will result in wrong values.
> >
> > For example, while this formula will work this month, it won't work
> > next month because the "92" will no longer be valid for the column I
> > need:
> >
> > =VLOOKUP(DD5,\$A\$1:\$CR\$1000,92,FALSE)
> >
> > The heading of the column I need to do the vlookup on in that formula
> > is "Avg MM Rate". So I had planned on using the following formula to
> > determine the column number of the column having that heading:
> >
> > =MATCH("Avg MM Rate",B1:CR1,0)+COLUMN(B1:CR1)-1
> >
> > and then substitute that formula for the "92" in the Vlookup formula,
> > resulting in:
> >
> > =VLOOKUP(DD5,\$A\$1:\$CR\$1000, MATCH("Avg MM Rate",B1:CR1,0)+COLUMN
> > (B1:CR1)-1,FALSE)
> >
> > However, that "MATCH" formula gave me 56 instead of 92. I checked
> > column 56 in the database and saw that it had the exact same column
> > heading as column 92. In fact, column 71 also has the same heading.
> > I discovered that several other column headings are duplicated or
> > triplicated in this database. (Obviously, this is a very badly
> > constructed database.)
> >
> > The designer of this "database" is not willing to make any changes
> > since he has been sending this file out to several hundred people each
> > month and says they're used to that format. So short of manually
> > making dozens of corrections to my copy of the database each month, I
> > was wondering if there was some alteration to my formula that might
> > work.
> >
> > Obviously, the MATCH formula is reading left to right in row 1 and
> > gives me the column number of the first match it finds. Is there a
> > way to get the 3rd occurrence of that heading? Or perhaps have the
> > formula read from right to left since it is the last occurrence of
> > that heading I want?
> >
> > Many thanks.

>
>
> The array formula (commit with CTRL+SHIFT+ENTER):
>
> =MAX(IF(1:1="Avg MM Rate",COLUMN(1:1),""))
>
> will give you the correct column number.
>

#12
February 13th 09, 03:21 PM posted to microsoft.public.excel.worksheet.functions
 Glenn external usenet poster Posts: 1,240
nth occurrence of MATCH

To get the "1:1" in the formula, you can just click on the row heading (the 1 at
the left of the worksheet). The same could be said for "row 14 in another
worksheet". It would look something like this:

'Sheet 3'!14:14

Chevron7 wrote:
> How can I use this formula for row 14 in another worksheet?
>

> "Glenn" wrote:
>
>> The array formula (commit with CTRL+SHIFT+ENTER):
>>
>> =MAX(IF(1:1="Avg MM Rate",COLUMN(1:1),""))
>>
>> will give you the correct column number.
>>

>> Paul D. Simon wrote:
>>> An Excel file that is now being sent to me each month contains a large
>>> database table containing several columns. Reports I generate from
>>> this file rely on Vlookup formulas. However, each month, the position
>>> of several columns changes due to additions and/or deletions of
>>> columns within the database from the previous month's file. So
>>> copying in my formulas each month will result in wrong values.
>>>
>>> For example, while this formula will work this month, it won't work
>>> next month because the "92" will no longer be valid for the column I
>>> need:
>>>
>>> =VLOOKUP(DD5,\$A\$1:\$CR\$1000,92,FALSE)
>>>
>>> The heading of the column I need to do the vlookup on in that formula
>>> is "Avg MM Rate". So I had planned on using the following formula to
>>> determine the column number of the column having that heading:
>>>
>>> =MATCH("Avg MM Rate",B1:CR1,0)+COLUMN(B1:CR1)-1
>>>
>>> and then substitute that formula for the "92" in the Vlookup formula,
>>> resulting in:
>>>
>>> =VLOOKUP(DD5,\$A\$1:\$CR\$1000, MATCH("Avg MM Rate",B1:CR1,0)+COLUMN
>>> (B1:CR1)-1,FALSE)
>>>
>>> However, that "MATCH" formula gave me 56 instead of 92. I checked
>>> column 56 in the database and saw that it had the exact same column
>>> heading as column 92. In fact, column 71 also has the same heading.
>>> I discovered that several other column headings are duplicated or
>>> triplicated in this database. (Obviously, this is a very badly
>>> constructed database.)
>>>
>>> The designer of this "database" is not willing to make any changes
>>> since he has been sending this file out to several hundred people each
>>> month and says they're used to that format. So short of manually
>>> making dozens of corrections to my copy of the database each month, I
>>> was wondering if there was some alteration to my formula that might
>>> work.
>>>
>>> Obviously, the MATCH formula is reading left to right in row 1 and
>>> gives me the column number of the first match it finds. Is there a
>>> way to get the 3rd occurrence of that heading? Or perhaps have the
>>> formula read from right to left since it is the last occurrence of
>>>
>>> Many thanks.

>>

#13
February 13th 09, 03:38 PM posted to microsoft.public.excel.worksheet.functions
 Chevron7 external usenet poster Posts: 4
nth occurrence of MATCH

Thank you; it worked just as you said.

Sorry about the additional posts but I was getting an error message saying
that my post was not accepted.

"Glenn" wrote:

> To get the "1:1" in the formula, you can just click on the row heading (the 1 at
> the left of the worksheet). The same could be said for "row 14 in another
> worksheet". It would look something like this:
>
> 'Sheet 3'!14:14
>
> Chevron7 wrote:
> > How can I use this formula for row 14 in another worksheet?
> >

>
> > "Glenn" wrote:
> >
> >> The array formula (commit with CTRL+SHIFT+ENTER):
> >>
> >> =MAX(IF(1:1="Avg MM Rate",COLUMN(1:1),""))
> >>
> >> will give you the correct column number.
> >>

>
> >> Paul D. Simon wrote:
> >>> An Excel file that is now being sent to me each month contains a large
> >>> database table containing several columns. Reports I generate from
> >>> this file rely on Vlookup formulas. However, each month, the position
> >>> of several columns changes due to additions and/or deletions of
> >>> columns within the database from the previous month's file. So
> >>> copying in my formulas each month will result in wrong values.
> >>>
> >>> For example, while this formula will work this month, it won't work
> >>> next month because the "92" will no longer be valid for the column I
> >>> need:
> >>>
> >>> =VLOOKUP(DD5,\$A\$1:\$CR\$1000,92,FALSE)
> >>>
> >>> The heading of the column I need to do the vlookup on in that formula
> >>> is "Avg MM Rate". So I had planned on using the following formula to
> >>> determine the column number of the column having that heading:
> >>>
> >>> =MATCH("Avg MM Rate",B1:CR1,0)+COLUMN(B1:CR1)-1
> >>>
> >>> and then substitute that formula for the "92" in the Vlookup formula,
> >>> resulting in:
> >>>
> >>> =VLOOKUP(DD5,\$A\$1:\$CR\$1000, MATCH("Avg MM Rate",B1:CR1,0)+COLUMN
> >>> (B1:CR1)-1,FALSE)
> >>>
> >>> However, that "MATCH" formula gave me 56 instead of 92. I checked
> >>> column 56 in the database and saw that it had the exact same column
> >>> heading as column 92. In fact, column 71 also has the same heading.
> >>> I discovered that several other column headings are duplicated or
> >>> triplicated in this database. (Obviously, this is a very badly
> >>> constructed database.)
> >>>
> >>> The designer of this "database" is not willing to make any changes
> >>> since he has been sending this file out to several hundred people each
> >>> month and says they're used to that format. So short of manually
> >>> making dozens of corrections to my copy of the database each month, I
> >>> was wondering if there was some alteration to my formula that might
> >>> work.
> >>>
> >>> Obviously, the MATCH formula is reading left to right in row 1 and
> >>> gives me the column number of the first match it finds. Is there a
> >>> way to get the 3rd occurrence of that heading? Or perhaps have the
> >>> formula read from right to left since it is the last occurrence of
> >>> that heading I want?
> >>>
> >>> Many thanks.
> >>

>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post How to count occurrence? Eric Excel Discussion (Misc queries) 1 July 12th 07 05:47 AM Match Last Occurrence of Numeric Value and Count BACK to Previous Sam via OfficeKB.com Excel Worksheet Functions 4 November 24th 05 02:15 AM Match Each Numeric occurrence and Return Individual Rows of Data Sam via OfficeKB.com Excel Worksheet Functions 4 October 13th 05 04:22 AM Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 12:40 PM Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM

All times are GMT +1. The time now is 11:33 PM.