|
AVLOOKUP2, AVLOOKUPS2 & AVLOOKUPNTH Functions
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 (AVLOOKUP2)
or all the values found (AVLOOKUPS2)
or the Nth value found (AVLOOKUPNTH)
AVLOOKUP2, AVLOOKUPS2 and AVLOOKUPNTH are NOT case-sensitive.
The AVLOOKUP functions are multi-threaded, non-volatile, array functions.
AVLOOKUP Family Syntax
AVLOOKUP2(Lookup_Values, Lookup_Table, Answer_Columns, Sorted,
Exact_Match, Lookup_Columns, MemType_Name)
The first 3 parameters are required; the last 4 parameters are optional.
AVLOOKUPS2 (Lookup_Values, Lookup_Table, Answer_Columns, Sorted,
Exact_Match, Lookup_Columns)
The first 3 parameters are required; the last 3 parameters are optional.
AVLOOKUPNTH (Lookup_Values, Lookup_Table, Answer_Columns, Sorted,
Exact_Match, Lookup_Columns, Position)
The first 3 parameters are required; the last 4 parameters are optional.
Lookup_Values (required)
The value(s) to be found in the Lookup_Columns.
Can be a single value or multiple values arranged in columns (lookup in multiple lookup columns) and rows (return multiple answer Rows). A single value can be a constant or a cell 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 AVLOOKUP 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 AVLOOKUP functions will look for a row separately for each row of lookup values (Rows are treated as OR).
A single AVLOOKUP2 or AVLOOKUPNTH statement will return the same number of rows and columns of result values as there are rows in Lookup_Values and columns in Answer_Columns.
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.
Answer_Columns (required)
Specifies the column or columns in Lookup_Table that the AVLOOKUP functions will return values from for the row or rows that are found in the lookup operation.
Answer_Columns can be a constant, an array of constants, an expression or a reference.
If Answer_Columns evaluates to a number it will be treated as relative column number(s) within Lookup_Table.
If Answer_Columns evaluates to text then the text will be treated as column labels to be found in the first row of Lookup_Table.
If the column labels are not found the AVLOOKUP functions return #REF.
Sorted (optional, defaults to False)
Specifies whether the data in Lookup_Table is sorted on the first Lookup Column ascending, descending or is 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_Match (optional, defaults to True)
Use this optional parameter when you want the AVLOOKUP functions to find a row in Table_range that exactly matches the Lookup_Value(s), even with sorted data, and also 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 is 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, 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 AVLOOKUP2 will first check to see if the index stored in memory that gave the answer the last time the AVLOOKUP2 was calculated still gives the correct answer. If it does then AVLOOKUP2 will return that answer without doing any more processing.
When using Lookup memory and there are multiple exact match answers available AVLOOKUP2 will not necessarily return the answer from the same row as VLOOKUP.
AVLOOKUPS and AVLOOKUPNTH do not use Lookup Memory.
Position (Optional, Defaults to 0, AVLOOKUPNTH only)
Controls which result will be returned when there are multiple rows that match the Lookup_Values..
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 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
AVLOOKUP2 returns the first row 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 AVLOOKUP2 will return the same row as in the previous calculation provided it still matches the Lookup_Value.
AVLOOKUPS2 with one or more rows of Lookup_Values, and AVLOOKUP2 or 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.
AVLOOKUPS2 Limits
Excel 2000 limits array functions like AVLOOKUPS2 to returning a maximum of 5461 values.
AVLOOKUPS2 returns #Value when this limit is reached.
You should be careful when returning multiple rows that this limit will not be reached.
There is a registry change and fix for Excel 2000 that eliminates this 5461 values limit. See Microsoft Knowledge Base article Q250828
|
