Home |
Search |
Today's Posts |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count occurrence? | Excel Discussion (Misc queries) | |||
Match Last Occurrence of Numeric Value and Count BACK to Previous | Excel Worksheet Functions | |||
Match Each Numeric occurrence and Return Individual Rows of Data | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions |