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 
#1




nth occurrence of MATCH
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. 
Ads 
#2




nth occurrence of MATCH
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. 
#3




nth occurrence of MATCH
Hi,
Let me understand you want to find the third occurrence of your string ("Avg MM Rate" in row 1 and use that column as the lookuo vector. Try this =VLOOKUP(DD5,$A$1:$CR$1000,COLUMN(INDEX(B1:CR1,LAR GE((B1:CR1="Avg MM Rate")*COLUMN(B1:CR1),COUNTIF(B1:CR1,"Avg MM Rate")+13)))1,FALSE) It's now an array formula 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must reenter as An array Mike "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. > 
#4




nth occurrence of MATCH
> will give you the correct column number.
No it won't it will return the last match not the Nth Mike "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. > 
#5




nth occurrence of MATCH
Hi,
I should have added you currently want the 3rd match. To change that to another it's the last 3 in the formula so changing that to 4 gives the 4th but don't forget to reenter as an array. Mike "Mike H" wrote: > Hi, > > Let me understand you want to find the third occurrence of your string ("Avg > MM Rate" in row 1 and use that column as the lookuo vector. Try this > > =VLOOKUP(DD5,$A$1:$CR$1000,COLUMN(INDEX(B1:CR1,LAR GE((B1:CR1="Avg MM > Rate")*COLUMN(B1:CR1),COUNTIF(B1:CR1,"Avg MM Rate")+13)))1,FALSE) > > It's now an array formula > > 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT > 'just enter. If you do it correctly then Excel will put curly brackets around > 'the formula{}. You can't type these yourself. If you Edit the ranges > 'then you must reenter as An array > > > Mike > > "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. > > 
#6




nth occurrence of MATCH
From the OP:
"Or perhaps have the formula read from right to left since it is the last occurrence of that heading I want?" Mike H wrote: >> will give you the correct column number. > > No it won't it will return the last match not the Nth > > Mike > > "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. >> 
#7




nth occurrence of MATCH
Apologies you are correct i was focussed on the message header asking for the
Nth occurrence "Glenn" wrote: > From the OP: > > "Or perhaps have the formula read from right to left since it is the last > occurrence of that heading I want?" > > > > Mike H wrote: > >> will give you the correct column number. > > > > No it won't it will return the last match not the Nth > > > > Mike > > > > "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. > >> > 
#8




nth occurrence of MATCH
Glenn and Mike,
Thank you both so very much for your absolutely PERFECT solutions!! (And my apologies for any confusion caused by my subject line.) Glenn, your surprisingly very simple array formula does indeed give me the correct column number (92) of the last occurrence of that heading in row 1. Beautiful!! Mike, your full solution to my vlookup dilemma also worked perfectly, and your key to me about changing that last 3 to anything else I want is great because for some of the headings, I would change the 3 to a 2 or to a 4. So both solutions are absolutely perfect in that sometimes I'd simply want the last occurrence regardless of what occurrence number it is, and sometimes I'd specifically want the exact nth occurence. Thanks again to both of you for taking the time and effort to solve my problem for me  I appreciate it very much! 
#9




nth occurrence of MATCH
How can I use this formula for 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. > 
#10




nth occurrence of MATCH
How can I use this formula for 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. > 
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 03: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 