|
The 24 Advanced Function Lookups
If you need more powerful functions use these advanced Lookup features:
High-performance multi-threaded lookups to exploit multiple cores
Written in C++ for ultimate performance
Run with 32 or 64 bit Excel
3 multi-threaded memory options
Return the first, last or Nth match from multiple answers (does not use lookup memory)
Return all matching answers where duplicates exist in the Lookup Columns to either multi-cell array formulas or aggregating functions like SUM, AVERAGE, MEDIAN, COUNT etc.
Two-dimensional Lookup: lookup both rows and columns
Multi-column Lookup (AND): lookup a row based on more than one column.
Multi-row Lookup (OR): Allows for multiple rows of lookup values and multiple lookup answers. Use multi-row Lookups in array formulas or in formulas like SUM, AVERAGE, MEDIAN, COUNT
Use multiple answer columns to return values from multiple columns.
The lookup column(s) can be any column(s) in the Lookup_Table
Specify the Lookup Column(s) and Answer Column(s) using Column header labels or column numbers.
Choice of case-sensitive or not case-sensitive matching
Lookups can use regular expressions for text exact match lookup
Lookup_Table can be sorted ascending, descending or not sorted
Lookup_Table can be a range or an array or a calculated range or an expression yielding an array.
Separate Sort and Exact options allow Exact Match with sorted data
Specify the error returned when Exact Match cannot find a matching row
Use wildcard characters * and # in text exact match lookup.
The Advanced Lookups Flavors
The advanced LOOKUP functions are combined in a number of different flavors.
AVLOOKUP2 and AMATCH2
Return all matches; return the Nth match and the standard match return.
oFor example AMATCHES2, AMATCHNTH, AMATCH2
Case-sensitive and non-case-sensitive
oFor example Case.AVLOOKUP2, Case.AVLOOKUPNTH, Case.AVLOOKUPS2
Regular Expression lookup and standard lookup
oFor example Rgx.Case.AMATCH2, Rgx.AVLOOKUPS2, Rgx.AMATCHNTH
All combinations of these flavors make 24 different advanced lookup functions in addition to MEMLOOKUP, MEMMATCH and COMPARE.LISTS.
|
