hLookUp() function

hLookUp(v, array, n, [type])
hLookUp(v, array, n, [type], [startIndex], [occurrence])

The hLookUp() function searches the top row of 'array' for 'v' and - if found - returns a value from the same column and the n-th row of 'array'.

The 2nd Match() variant uses two additional parameters:
startFrom - positive numbers specify where the searching should start and negative numers specify where it should end. For the first, top-left cell of the searched range 'startFrom'=1, for the 2nd one 'startFrom'=2 etc. For the last, bottom-right cell of the searched range 'startFrom'=-1, for the preceeding cell 'startFrom'=-2 etc.
occurrence - specifies which occurrence of the matching/found value should be used. Positive values indicate top-down searching and counting. Negative values indicate bottom-up searching and counting. For the first match 'occurrence'=1, for the 2nd 'occurrence'=2 etc. For the last match 'occurrence'=-1, for the preceeding match 'occurrence'=-2 etc. The number of occurrences is counted from the 'startFrom' index.

The 'type' argument specifies how the searching procedure should be performed. It can be either one of the three values 0, -1, 1 or a combination (sum) of various 'SEARCH::' flags:

The '1' value is an equivalent to (SEARCH::SortAscending + SEARCH::MatchNotGreater).
The '-1' value is an equivalent to (SEARCH::SortDescending + SEARCH::MatchNotSmaller).
The '0' value is an equivalent to (0).
If 'type' is omitted, it's assumed to be 1.

The SEARCH::Pattern and SEARCH::RegEx flags can't be used with SEARCH::MatchNotGreater, SEARCH::MatchNotSmaller, SEARCH::StringSort, SEARCH::CaseSensitive, SEARCH::SortAscending, SEARCH::SortDescending.

The SEARCH::MatchNotGreater and SEARCH::MatchNotSmaller flags can not be used with the SEARCH::FirstMatch and SEARCH::LastMatch flags.

If neither SEARCH::FirstMatch nor SEARCH::LastMatch is specified, the linear search returns the first match and the quick search may return any of the existing matches.

If SEARCH::SortAscending or SEARCH::SortDescending is specified, the searched range either should not contain any formulas or the formulas should not break the sort order during the recalculation. Additionally, in such a case, no circular reference will be reported for cells other than the result cell.

Typically, quick binary searches enabled by specifying the SEARCH::SortAscending or SEARCH::SortDescending flags should be significantly (tens/hundreds of times) faster then the plain linear searches.

If SEARCH::MixedData is specified, the searched range is assumed to contain both numeric and text cells. If the search value is a text string, all values from that range will be (internally) converted to text strings then compared. If it's a number, all text strings that represent numbers will be converted to numbers before the comparison takes place.
If any of the two sorting flags is used along with the SEARCH::MixedData flag, the searched range must be sorted using such a "mixed" text/numeric method.

If a given workbook is to contain an extremely large number of hLookUp() functions, for better performance, when specifying the above options one can use the resulting numeric code instead of the individual option names.

If the match is not found, it returns the #N/A! error value.

hLookUp() examples:

=hLookUp(2, {1, 2, 3; "a", "b", "c"}, 2, 0) returns "b"

=hLookUp(2.5, {1, 2, 3; "a", "b", "c"}, 2, -1) returns "c"

=hLookUp(2.5, {1, 2, 3; "a", "b", "c"}, 2, 1) returns "b"

=hLookUp("*bc??", {"abc", "abcde", "ac"; 1, 2, 3}, 2, 0) returns 2

=hLookUp(2.5, sheet1!b5:d10000, 2, SEARCH::SortAscending + SEARCH::MatchNotGreater)

=hLookUp("bc\d", {"abc", "abcde", "abc10"; 1, 2, 3}, 2, SEARCH::RegEx,,) returns 3

You can insert the hLookUp() function with just two clicks using the Insert > HLOOKUP() command. The displayed "Insert HLOOKUP()" dialog box determines the optimum parameters, pre-set all the options and automatically creates the formula.

The source range 16 element list is a global list created and stored in the settings file. Subsequent source ranges are added in a circular manner by clicking one of the following:

The "Copy as Location" menu command

The "Enter" toolbar button