|
AMATCH2, AMATCHES2 & 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 (AMATCHNTH)
These functions are similar to the AVLOOKUP2, AVLOOKUPS2 and AVLOOKUPNTH functions except that they return relative row numbers rather than values.
These functions are NOT case-sensitive.
The AMATCH functions are multi-threaded, non-volatile array functions.
AMATCH Family Syntax
AMATCH2 (Lookup_Values, Lookup_Table, Sorted,
Exact_Match, Lookup_Columns, MemType_Name)
The first 2 parameters are required; the last 4 parameters are optional.
AMATCHES2 (Lookup_Values, Lookup_Table, Sorted,
Exact_Match, Lookup_Columns)
The first 2 parameters are required; the last 3 parameters are optional.
AMATCHNTH (Lookup_Values, Lookup_Table, Sorted,
Exact_Match, Lookup_Columns, Position)
The first 2 parameters are required; the last 4 parameters are optional.
AMATCH2, AMATCHES2 and AMATCHNTH return row number(s) within the range specified by Lookup_Table.
Lookup_Values (required)
Specifies the value(s) to be found in the Lookup_Columns. Can be a single value or multiple values arranged in columns (multiple lookup columns) and rows (multiple Lookup Rows).
A single value can be a constant or a range reference.
Multiple lookup values 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 lookup values as there are columns in the Lookup_Columns.
When doing multi-column lookups (multiple columns of both lookup values and lookup columns) the AMATCH functions will look for a row where ALL the lookup values are matched in the corresponding columns (Columns are treated as AND).
When doing multi-row lookups (multiple rows of lookup values) the AMATCH functions will look for a row separately for each row of lookup values (Rows are treated as OR).
A single AMATCH2 or AMATCHNTH statement will return the same number of row numbers as there are rows in Lookup_Values.
Lookup_Values can contain the wildcard characters ? and * for exact matches on unsorted text data. To find actual question marks or asterisks add a tilde (~) preceding the character.
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.
Sorted (Optional, defaults to False)
Specifies whether the data in Lookup_Table is sorted on the first Lookup Column ascending, descending or not sorted. Valid values for Sorted are:
True, “Asc”, “Yes”, “True”, 1 Ascending
“Des”, -1 Descending
False, “No”, 0, any other text Not Sorted
If the Lookup_Table is sorted on the first Lookup_Column the lookup process will be significantly faster.
Exact_Error (optional, defaults to True)
Use this optional parameter when you want the AMATCH functions to find a row in Lookup_Table that exactly matches the Lookup_Value(s), even with sorted data, and to specify what to return if an exact match does not exist.
An exact match will always be done with unsorted data.
A value of False means that an approximate match will be found with sorted data.
Use True to find an exact march with sorted data and return #N/A if not found.
An exact match on sorted data is much more efficient than an exact match on unsorted data.
If a value of anything other than False is specified it will indicate that an exact match is to be done even with sorted data, and the value given specifies the value to be returned if no exact match can be found.
If True is specified or 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 Lookup_Values.
Multiple columns can be given either as an array of constants or as a range reference.
MemType_Name (Optional, Defaults to 2, 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 AMATCH2 will first check to see if the index stored in memory that gave the answer the last time the AMATCH2 was calculated still gives the correct answer. If it does then AMATCH2 will return that answer without doing any more processing.
When using Lookup memory and there are multiple exact match answers available AMATCH2 will not necessarily return the answer from the same row as MATCH.
AMATCHES2 and AMATCHNTH do not use lookup memory.
Position (Optional, Defaults to 0, 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 then the largest value that is less than or equal to Lookup_Value
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
AMATCH2 returns the first row number that it finds which meets these criteria:
Sorted Ascending – the largest value that is less than or equal to Lookup_Value
Sorted Descending – the smallest value that is greater than or equal to Lookup_Value.
Not Sorted – The first row containing a value equal to Lookup_Value, except when using the built-in memory function. In this case AMATCH2 will return the same row as in the previous calculation provided it still matches the Lookup_Value.
AMATCHES2 with one or more rows of Lookup_Values, and AMATCH2 or 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.
|
