|
Rgx.Case.AMATCH2, Rgx.Case.AMATCHES2 & Rgx.Case.AMATCHNTH functions
Search for values in one or more columns of a table, and return the relative position of the row(s) where a match is found.
Advanced MATCH functions returning:
either the relative position of the first value found (AVMATCH2)
or the relative position of all the values found (AVMATCHES2)
or the relative position of the Nth value found (Rgx.Case.AMATCHNTH)
These functions are similar to the Rgx.AVLOOKUP2, Rgx.AVLOOKUPS2 and Rgx.AVLOOKUPNTH functions except that they return row numbers rather than values.
These functions are case-sensitive versions of Rgx.AMATCH2, Rgx.AMATCHES2 and Rgx.AMATCHNTH.
The Rgx.Case.AMATCH family of functions are multi-threaded, non-volatile array functions.
Rgx.Case.AMATCH Family Syntax
Rgx.Case.AMATCH2 (RegExp, Lookup_Table,
Not_Found, Lookup_Columns, MemType_Name)
The first 3 parameters are required; the last 3 parameters are optional.
Rgx.Case.AMATCHES2 (RegExp, Lookup_Table,
Lookup_Columns)
The first 2 parameters are required; the last parameter is optional.
Rgx.Case.AMATCHNTH (RegExp, Lookup_Table,
Not_Found, Lookup_Columns, Position)
The first 2 parameters are required; the last 3 parameters are optional.
Rgx.Case.AMATCH2, Rgx.Case.AMATCHES2 and Rgx.Case.AMATCHNTH return row number(s) within the range specified by Lookup_Table.
RegExp (required)
Specifies the Regular Expression Pattern(s) to be matched against the value in the Lookup_Column(s).
Can be a single Regular Expression or multiple Regular Expressions arranged in columns (multiple lookup columns) and rows (multiple Lookup Rows).
A single Regular Expression can be a constant or a cell reference.
Multiple lookup Regular Expressione can be specified either as an array of constants or as a range referring to multiple cells. There should be the same number of columns of Regular Expression values as there are columns in the Lookup_Columns.
When doing multi-column lookups (multiple columns of both lookup values and lookup columns) the Rgx.Case.AMATCH functions will look for a row where ALL the lookup Regular Expression patterns are matched in the corresponding columns (Columns are treated as AND).
When doing multi-row lookups (multiple rows of lookup values) the Rgx.Case.AMATCH functions will look for a row separately for each row of lookup values (Rows are treated as OR).
A single Rgx.Case.AMATCH2 or Rgx.Case.AMATCHNTH statement will return the same number of rows and columns of result values as there are rows and columns in p.
Lookup_Table (required)
The rectangular range of cells or array or expression yielding an array to be used for the lookup table. The array/range must resolve to a single contiguous rectangular array.
Not_Found (optional, defaults to False)
Use this optional parameter to specify what to return if an exact match does not exist.
If the parameter is omitted the error value will be #N/A.
Lookup_Columns (optional, defaults to 1)
Use this optional parameter when you want to control which column(s) in Lookup_Table to use for the lookup.
The default value is 1: the first column in Lookup_Table
If the values are numeric they will be treated as column numbers, if alphabetic they will be treated as column labels to be found in the first row of Lookup_Table.
If there is more than one column given then there must be a matching set of values given in the columns of RegExp.
Multiple columns can be given either as an array of constants or as a range reference.
MemType_Name (Optional, Defaults to 2, Rgx.Case.AMATCH2 only)
Memory type for lookups can be 0, 1, 2 or 3 or a string that is used as the name for a memory.
3 = Global Memory for Rows or Columns A single index is stored in global memory for each row (vertical lookups) or column (horizontal lookups).
This global memory is super-efficient and easy to use when you have more than one lookup formula on the same row looking up different columns in a Table. This global memory works across all worksheets in all open workbooks.
2 = Book Sheet Row Memory If you are using different Lookup Tables on different worksheets but still want to have more than one lookup formula on a row looking up different columns in the same table you can use this option which stores the row memory separately for each Workbook worksheet.
1 = Book Sheet Cell Memory If you are using multiple Lookup Tables formulas in different cells on the same sheet and the same row you should use this option which stores the memory separately for each cell.
0 = Do not use Lookup memory
Named Memory If you give a text string instead of a number it will be used as a named memory. You can have many named memories in use at the same time (usually one for each Lookup Table). Each named memory works by row across all sheets within a workbook, so is the best choice when you have:
oMore than one memory lookup referring to different lookup tables within a single formula.
oMore than one memory lookup referring to different lookup tables in different cells on the same row, and these lookups are repeated on the same row in multiple sheets.
If this option is not zero, then Rgx.Case.AMATCH2 will first check to see if the index stored in memory that gave the answer the last time the Rgx.Case.AMATCH2 was calculated still gives the correct answer. If it does then Rgx.Case.AMATCH2 will return that answer without doing any more processing.
When using Lookup memory and there are multiple exact match answers available Rgx.Case.AMATCH2 will not necessarily return the answer from the same row as MATCH.
Rgx.Case.AMATCHES2 and Rgx.Case.AMATCHNTH do not use lookup memory.
Position (Optional, Defaults to 0, Rgx.Case.AMATCHNTH only)
Controls which result will be returned from multiple matches.
N: where N is a positive integer. The Nth match found will be returned
0 : If sorted ascending the largest value that is less than or equal to RegExp
If not sorted then the first value found
If sorted descending then the smallest value that is greater than or equal to lookup value
-1: The first value found will always be returned
-2: the Last Value found will always be returned
-3: All matches found will be returned
Remarks
Rgx.CaseAMATCHES2 with one or more rows of Lookup_Values, and Rgx.Case.AMATCH2 or Rgx.Case.AMATCHNTH with multiple rows of Lookup Values can return a variable number of rows depending on how many rows meet the lookup criteria. So you should use them either in an array formula that returns multiple rows or embedded in functions like SUM(), AVERAGE() etc. that can handle arrays containing a variable number of results.
|
