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

The vLookUp() 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:

• 0 - vLookUp searches a given range linearly for an exact match; 'v' can be a search pattern containing '?' (any single character) and '*' (any string, including an empty string); to search for '?' or '*' place a tilde (~) before them.
• 1 - if an exact match is not found, vLookUp will search for the largest value that is not greater than 'v'; no pattern matching is performed; the searched range must be sorted in the ascending order.
• -1 - if an exact match is not found, vLookUp will search for the smallest value than is not smaller than 'v'; no pattern matching is performed; the searched range must be sorted in the descending order.
• SEARCH::MatchNotGreater (or 2) - if an exact match is not found, the function will search for the largest value that is not greater than 'v'.
• SEARCH::MatchNotSmaller (or 4) - if an exact match is not found, the function will search for the smallest value than is not smaller than 'v'.
• SEARCH::SortAscending (or 8) - perform a quick binary search for a range that is sorted in the ascending order.
• SEARCH::SortDescending (or 16) - perform a quick binary search for a range that is sorted in the descending order.
• SEARCH::CaseSensitive (or 128) - use case sensitive string comparison.
• SEARCH::FirstMatch (or 256) - find the first match.
• SEARCH::LastMatch (or 512) - find the last match.
• SEARCH::AutoSort (or 1024) - perform background sorting automatically during the first update then use the quick binary searches. In this case the startFrom parameter refers to the internally sorted range.
• SEARCH::MixedData (or 2048) - the searched range contains both text and numbers.
• SEARCH::SortIndex (or 4096) - can only be used with SEARCH::AutoSort; if it's specified, Match() will return the index ralated to the internally sorted searched range, not to the actual un-sorted range in the worksheet.
• SEARCH::RegEx (or 8192) - the 'v' parameter is a regular expression.
• "SEARCH::IgnorePunctuation (or 16384) - use the word sort order (ignoring certain punctuation marks).
• "SEARCH::NeutralSortOrder (or 32768) - use neutral, language independent string comparison instead of the default language specific comparison.
• "SEARCH::Pattern (or 65536) - the \"v\" parameter is a simple (wildcard) pattern; can't be used with fast binary searching.

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 must not contain any formulas or the formulas must 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::AutoSort is specified, GS-Calc will be creating and maintaining sort indices for the referenced searched ranges contaning unsorted data. Thanks to those internally created indices it's possible to use quick binary searches for data that doesn't have to be sorted manually by the user. All sort indices are created during the first update and then they are individually updated whenever it's necessary.
The speed gain depends on how many vLookUp() formulas are there in your worksheet, how often the same ranges are re-used and how big the searched ranges are. For very large worksheets, this can be even hundreds of times (and more) faster.
The SEARCH::AutoSort flag must be used with SEARCH::SortAscending or SEARCH::SortDescending. If the searched data is already partially sorted, it's recommeneded that you use the sort order flag that matches that partial sorting the best.

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 the SEARCH::AutoSort flag is used, GS-Calc will take care about the correct internal sorting, however this option will cause significant slowing down when used with the "AutoSort" option. Otherwise use the respective options in the Tools > Sort Cell Range dialog box.

If a given workbook is to contain an extremely large number of vLookUp() 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.

vLookUp() examples:

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

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

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

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

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

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

You can insert the vLookUp() function with just two clicks using the Insert > VLOOKUP() command. The displayed "Insert VLOOKUP()" 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