Difference between revisions of "Documentation/How Tos/Calc: VLOOKUP function"

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎See also:)
m
 
(8 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
__NOTOC__
+
{{DISPLAYTITLE:VLOOKUP function}}
 +
{{Documentation/CalcFunc SpreadsheetTOC
 +
|ShowPrevNext=block
 +
|PrevPage=Documentation/How_Tos/Calc:_OFFSET_function
 +
|NextPage=Documentation/How_Tos/Calc:_AREAS_function
 +
}}__NOTOC__
  
 
== VLOOKUP  ==
 
== VLOOKUP  ==
Line 16: Line 21:
 
: If [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expressions]] are enabled on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog, <tt>'''VLOOKUP'''</tt> will find exact matches treating <tt>'''lookupvalue'''</tt> as a [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expression]]. This only makes sense, and should only be used, if <tt>'''mode'''</tt> is <tt>'''0'''</tt> or <tt>'''FALSE'''</tt>.
 
: If [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expressions]] are enabled on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog, <tt>'''VLOOKUP'''</tt> will find exact matches treating <tt>'''lookupvalue'''</tt> as a [[Documentation/How_Tos/Regular_Expressions_in_Calc|regular expression]]. This only makes sense, and should only be used, if <tt>'''mode'''</tt> is <tt>'''0'''</tt> or <tt>'''FALSE'''</tt>.
  
−
: If '<i>Search criteria = and <> must apply to whole cells</i>' is enabled on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog, <tt>'''lookupvalue'''</tt> must match the whole text in the cell; if not, it can match just part of the text.
+
: If '<i>Search criteria = and <> must apply to whole cells</i>' is enabled on the {{menu|Tools|Options|OpenOffice Calc|Calculate}} dialog, <tt>'''lookupvalue'''</tt> must match the whole text in the cell; if not, it can match just part of the text.
  
−
: Matching is <u>always case-insensitive</u> - the case setting on the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog does not apply.
+
: Matching is <u>always case-insensitive</u> - the case setting on the {{menu|Tools|Options|OpenOffice Calc|Calculate}} dialog does not apply.
  
 
=== Examples: ===
 
=== Examples: ===
Line 66: Line 71:
  
 
<tt>'''VLOOKUP("at"; A1:B5; 2; 1)'''</tt>
 
<tt>'''VLOOKUP("at"; A1:B5; 2; 1)'''</tt>
−
: returns <tt>'''B'''</tt>, even if '<i>Search criteria = and <> must apply to whole cells</i>' is disabled. In this case the match between <tt>'''at'''</tt> and <tt>'''cat'''</tt> is ignored in favour of treating <tt>'''at'''</tt> as a word to come between <tt>'''5'''</tt> and <tt>'''cat'''</tt>.
+
: returns <tt>'''B'''</tt>, even if '<i>Search criteria = and <> must apply to whole cells</i>' is disabled. In this case the match between <tt>'''at'''</tt> and <tt>'''cat'''</tt> is ignored in favor of treating <tt>'''at'''</tt> as a word to come between <tt>'''5'''</tt> and <tt>'''cat'''</tt>.
  
−
=== See also: ===
+
=== Issues: ===
−
'''[[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]''',
+
*The case sensitivity behavior is discussed in {{bug|71000}}
−
'''[[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]]''',
+
−
'''[[Documentation/How_Tos/Calc: INDEX function|INDEX]]''',
+
−
'''[[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]''',
+
−
'''[[Documentation/How_Tos/Calc: MATCH function|MATCH]]''',
+
−
'''[[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]'''
+
  
−
'''[[Documentation/How_Tos/Calc: PROB function|PROB]]'''
+
{{SeeAlso|EN|
−
 
+
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]]
−
'''[[Documentation/How_Tos/Regular_Expressions_in_Calc|Regular Expressions in Calc]]'''
+
* [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]]
−
 
+
* [[Documentation/How_Tos/Calc: INDEX function|INDEX]]
−
'''[[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]'''
+
* [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]]
−
 
+
* [[Documentation/How_Tos/Calc: MATCH function|MATCH]]
−
[[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']],
+
* [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]]
−
[[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']]
+
* [[Documentation/How_Tos/Calc: PROB function|PROB]]
−
 
+
* [[Documentation/How_Tos/Regular_Expressions_in_Calc|Regular Expressions in Calc]]
−
=== Issues: ===
+
* [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]
−
*The case sensitivity behaviour is discussed in [http://qa.openoffice.org/issues/show_bug.cgi?id=71000 issue 71000]
+
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 +
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 +
[[Category: Documentation/Reference/Calc/Spreadsheet functions]]

Latest revision as of 11:20, 2 February 2024



VLOOKUP

Returns a value from a table column, in the row found by lookup in the first column.

Syntax:

VLOOKUP(lookupvalue; datatable; columnindex; mode)

lookupvalue is a value (number, text or logical value) to look up in the left column of the range/array datatable. When a value is matched in the left column, VLOOKUP returns the corresponding value (in the same row) in the columnindexth column of datatable, where columnindex = 1 is the left column.
If mode is 0 or FALSE, the left column of datatable may be unordered, and the first exact match is found (searching from the top).
If mode is 1 or TRUE, or is omitted, the left column of datatable must be sorted, with numbers in ascending order appearing before text values in alphabetic order. VLOOKUP decides where in the left column lookupvalue would appear. If there is an exact match, that is the row found; if there is more than one exact match, the row found is not necessarily nearest the top. If there is no exact match, the row above where value would appear in the left column is found; the #N/A error results if that row is not in the table.


If regular expressions are enabled on the Tools - Options - OpenOffice.org Calc - Calculate dialog, VLOOKUP will find exact matches treating lookupvalue as a regular expression. This only makes sense, and should only be used, if mode is 0 or FALSE.
If 'Search criteria = and <> must apply to whole cells' is enabled on the Tools → Options → OpenOffice Calc → Calculate dialog, lookupvalue must match the whole text in the cell; if not, it can match just part of the text.
Matching is always case-insensitive - the case setting on the Tools → Options → OpenOffice Calc → Calculate dialog does not apply.

Examples:

In these examples, cells A1 to A5 contain 3, 5, cat, mat, matter, and cells B1 to B5 contain A, B, C, D, E.

Calc vlookup example.png

VLOOKUP(3; A1:B5; 2; 0)

returns A. Cell A1 in the left column matches 3. The contents of the corresponding cell in the 2nd column (that is, B1) are returned. The left column happens to be sorted, but this is not necessary in mode 0.

VLOOKUP(4; A1:B5; 2; 0)

returns #N/A. There is no match for 4.

VLOOKUP("CAT"; A1:B5; 2; 0)

returns C. The difference between lower and upper case is ignored, so CAT matches cat.

VLOOKUP("c.t"; A1:B5; 2; 0)

returns C if regular expressions are enabled (and #N/A if not). The dot '.' stands for 'any single character' in a regular expression, so c.t matches cat.

VLOOKUP(".at"; A1:B5; 2; 0)

returns C if regular expressions are enabled. cat matches; so does mat, but the match nearest the top is always found in mode 0.

VLOOKUP("at"; A1:B5; 2; 0)

returns #N/A if 'Search criteria = and <> must apply to whole cells' is enabled and C otherwise. at does not match any whole cell contents, but it matches part of cat.

VLOOKUP(3; A1:B5; 2; 1)

returns A. Cell A1 in the left column matches 3. The left column must be sorted in mode 1, with text coming after numbers. matter follows mat because although the first letters are the same, matter has more letters.

VLOOKUP(4; A1:B5; 2; 1)

returns A. 4 would appear between 3 and 5 in the left column, so the position above is found.

VLOOKUP(1; A1:B5; 2; 1)

returns #N/A. 1 would appear before 3 in the left column, and there is no position above that in the table.

VLOOKUP(7; A1:B5; 2; 1)

returns B. 7 would appear between 5 and cat in the left column, so the position above is found.

VLOOKUP("apple"; A1:B5; 2; 1)

returns B. apple would appear between 5 and cat in the left column, so the position above is found.

VLOOKUP("mate"; A1:B5; 2; 1)

returns D. mate would appear between mat and matter in the left column, so the position above is found.

VLOOKUP("rat"; A1:B5; 2; 1)

returns E. rat would appear after matter in the left column, so the position above is found.

VLOOKUP("at"; A1:B5; 2; 1)

returns B, even if 'Search criteria = and <> must apply to whole cells' is disabled. In this case the match between at and cat is ignored in favor of treating at as a word to come between 5 and cat.

Issues:


See Also
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_VLOOKUP_function&oldid=259986"
Views
Personal tools
Navigation
Tools