|
Rgx.AVLOOKUP2, Rgx.AVLOOKUPS2 & Rgx.AVLOOKUPNTH Functions
Uses Regular Expressions to search for values in one or more columns of a table, and return values from the rows where a match is found.
Advanced Lookup functions returning:
either the first value found (Rgx.AVLOOKUP2)
or all the values found (Rgx.AVLOOKUPS2)
or the Nth value found (Rgx.AVLOOKUPNTH)
Rgx.AVLOOKUP2, Rgx.AVLOOKUPS2 and Rgx.AVLOOKUPNTH are NOT case-sensitive.
These functions are the Regular Expression versions of AVLOOKUP2, AVLOOKUPS2 and AVLOOKUPNTH
The Rgx.AVLOOKUP family of functions are multi-threaded, non-volatile array functions.
Rgx.AVLOOKUP Family Syntax
Rgx.AVLOOKUP2(RegExp, Lookup_Table, Answer_Columns,
Not_Found, Lookup_Columns, MemType_Name)
The first 3 parameters are required; the last 3 parameters are optional.
Rgx.AVLOOKUPS2(RegExp, Lookup_Table, Answer_Columns,
Not_Found, Lookup_Columns)
The first 3 parameters are required; the last 2 parameters are optional.
Rgx.AVLOOKUPNTH(RegExp, Lookup_Table, Answer_Columns,
Not_Found, Lookup_Columns, Position)
The first 3 parameters are required; the last 3 parameters are optional.
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 Expressions 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.AVLOOKUP 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.AVLOOKUP functions will look for a row separately for each row of lookup values (Rows are treated as OR).
A single Rgx.AVLOOKUP2 or Rgx.AVLOOKUPNTH statement will return the same number of rows and columns of result values as there are rows and columns in RegExp.
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.
Answer_Columns (required)
Specifies the column or columns in Lookup_Table that Rgx.AVLOOKUP2 will return values from for the row or rows that are found in the lookup operation.
Can be a constant, an array of constants or an expression that returns a range.
If Answer_Columns is an expression that returns a number it will be treated as column number(s) within Lookup_Table.
If Answer_Columns returns text this will be treated as column labels to be found in the first row of Lookup_Table. If the column labels are not found Rgx.AVLOOKUP2 returns #REF.
Not_Found (optional, defaults to #N/A)
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.AVLOOKUP2 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.AVLOOKUPS2 will first check to see if the index stored in memory that gave the answer the last time the Rgx.AVLOOKUPS2 was calculated still gives the correct answer. If it does then Rgx.AVLOOKUPS2 will return that answer without doing any more processing.
When using Lookup memory and there are multiple exact match answers available Rgx.AVLOOKUPS2 will not necessarily return the answer from the same row as MATCH.
Rgx.AVLOOKUPS2 and Rgx.AVLOOKUPNTH do not use lookup memory.
Position (Optional, Defaults to 0, Rgx.AVLOOKUPNTH 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 : The first value found will always be returned
-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.AVLOOKUPS2 with one or more rows of Lookup_Values, and Rgx.AVLOOKUP2 or Rgx.AVLOOKUPNTH 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.
|
