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. 


Thread Tools  Display Modes 
#11




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. > 
Ads 
#12




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 >>> that heading I want? >>> >>> Many thanks. >> 
#13




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  


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 