[Tutorial] VLOOKUP questions and answers

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Locked
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

[Tutorial] VLOOKUP questions and answers

Post by MrProgrammer »

There are several "lookup" functions in Calc: VLOOKUP, HLOOKUP, LOOKUP, and MATCH. This tutorial is for VLOOKUP but the others are easy to learn once you understand how VLOOKUP works. VLOOKUP searches for a value in the left column of a table and returns the value in the same row of another column of that table.

The syntax is =VLOOKUP( valueToSearchFor ; tableToSearchIn ; columnIdentifier ; isTableSorted? ).

valueToSearchFor - This is the value which VLOOKUP uses when searching tableToSearchIn. Normally it is a reference to a cell, but it could be a constant or an expression. That is, normally people use =VLOOKUP(D5;…;…;…) but you could use =VLOOKUP(3.14159;…;…;…) or =VLOOKUP(355/113;…;…;…).

tableToSearchIn - This is range of one or more rows and one or more columns. VLOOKUP searches for the valueToSearchFor in the first column of tableToSearchIn. Normally VLOOKUP's second parameter is a specific reference to a range of cells, like $F$2:$H$14. You almost certainly want to use an absolute range ($F$2:$H$14) rather than a relative range (F2:H14) so that the range is not adjusted if the VLOOKUP formula is copied to other cells. Read section 8 (Using formulas and cell references) in Ten concepts that every Calc user should know if you don't understand the difference between absolute and relative references.

columnIdentifier - This identifies the column used for the function result after the search has located a value in the first column of tableToSearchIn. For VLOOKUP, the columns of tableToSearchIn are identified as 1, 2, 3, …. So for the range $F$2:$H$14, columnIdentifier 1 is F, columnIdentifier 2 is G, and columnIdentifier 3 is H. =VLOOKUP(…;$F$2:$H$14;2;…) means to perform the search in column F and return the value in the same row of column G. A columnIndentifier of 3 would mean to return the value in the same row of column H.

isTableSorted? - Based on the questions asked in this forum, almost everyone should supply 0 as the fourth parameter of VLOOKUP, which means that you want an exact match for valueToSearchFor. Since 0 is not the default, be sure not to omit it. There are questions below that address what would happen if you did.

Example: C2 contains formula =VLOOKUP(B2;$F$2:$H$14;3;0). This means: look for B+ in column F and return the corresponding value from column H, which is 3.3. Note that the fourth parameter is 0.
Screen shot 2012-01-01 at 21.14.12 .png
VLOOKUP.ods
(8.85 KiB) Downloaded 7610 times
Very important: Two of the options (OpenOffice.org > Preferences on a Mac, Tools > Options on other platforms) affect several functions, including VLOOKUP. Both of these options are in the Calc > Calculate section:
  • Search criteria = and <> must apply to whole cells — If you uncheck this option text searches in VLOOKUP can match a substring of the values in the table so in the example a search for B will find B+. You almost certainly want to enable this option so that an exact match must occur. Enabling the option also makes your VLOOKUP formulas compatible with Excel.
  • Enable regular expressions in formulas — Unless you understand what "regular expressions" are (see Help) and unless you specifically want to use them in your spreadsheet, you will want to uncheck Enable regular expressions in formulas because this option can make VLOOKUP difficult to use. Unchecking the option also makes your VLOOKUP formulas compatible with Excel. The questions below address what happens if you enable this option.
  • Summary: Check Search whole cells and uncheck Regular expressions
Q1. What happens if =VLOOKUP(…;…;…;0) can't match the valueToSearchFor?
A1. It returns the error "Value not available" (#N/A).

Q2. I don't want to see #N/A. How can I get VLOOKUP to leave the cell empty if it can't find valueToSearchFor?
A2. Use =IF(ISNA(VLOOKUP(…;…;…;0));"";VLOOKUP(…;…;…;0)). Or put =VLOOKUP(…;…;…;0) in an offscreen column (say ZA2) and use =IF(ISNA(ZA2);"";ZA2) in the main part of your spreadsheet. This means:
  • If VLOOKUP returned #N/A, the cell's value is the empty string (""); otherwise the cell's value is the VLOOKUP result.
Q3. How can I provide a "default" value in tableToSearchIn, that is, a value that will be returned if valueToSearchFor isn't found?
A3. You can't put a default in the table, but you can put a default in the formula. Use =IF(ISNA(VLOOKUP(…;…;…;0));defaultValue;VLOOKUP(…;…;…;0)) or, using the technique above, =IF(ISNA(ZA2);defaultValue;ZA2).

Q4. Can I use VLOOKUP to tell if me if valueToSearchFor is found in a list? I don't need to do a table lookup; I just want a yes or no answer.
A4. Yes, use =NOT(ISNA(VLOOKUP(valueToSearchFor;yourList;1;0))). TRUE means the search was successful.

Q5. I can't get your example to work. It finds 4.3 instead of 4.0 for the lookup of A. Why doesn't it work for me?
A5. You probably didn't enable the Search criteria = and <> must apply to whole cells option so VLOOKUP found A+ in the table when searching for A.

Q6. I can't get your example to work. It finds 3.0 instead of 3.3 for the lookup of B+. Why doesn't it work for me?
A6. You probably didn't uncheck the Enable regular expressions in formulas option.

Q7. OK, unchecking option Enable regular expressions in formulas allows VLOOKUP to find 3.3 for B+. Why does VLOOKUP give the wrong answer if the option is checked?
A7. VLOOKUP is returning the correct answer in both situations. It's just that "regular expression" matching works differently. It is beyond the scope of this tutorial to explain regular expressions, but they are a way of performing pattern matching. In this particular case the pattern B+ matches any number of B's like B or BB or BBB. In a pattern, the plus sign means "at least one" and does not mean "+". So pattern B+ matches B in the table and the value is 3.0. Pattern B+ does not match B+ in the table.

Q8. Are there other pattern characters what I need to be careful about?
A8. Yes: any of .^$*+?\|[](){}  Because there are so many of them, it's best to uncheck Enable regular expressions in formulas because then they won't cause trouble for you. See other examples of pattern matching difficulties in Lookup table for grade calculation.

Q9. I have a table that I want to use with VLOOKUP but the column I want to search isn't the leftmost one. What can I do?
A9. The columnIdentifier must be a positive number so it's easiest if you restructure your table so it meets VLOOKUP's requirement. If you don't want to do that, you can use MATCH and INDEX as shown in [Solved] VLOOKUP to the left column?.

Q10. I see I can use a constant for the first parameter of VLOOKUP. Can I use a constant for the table (the second parameter) too?
A10. Yes, but you will need to use an array constant. See A20 (below) for an example.

Q11. I can't get your example to work. It corrrectly finds 3.3 for the lookup of B+, but 3.7 for the lookup of B-. I have unchecked Enable regular expressions in formulas. Why doesn't it work for me?
A11. Ensure that you supplied 0 as the fourth parameter for VLOOKUP. If you use a non-zero value, or if you omit the fourth parameter, you're telling VLOOKUP that tableToSearchIn is sorted in ascending order. The table in the tutorial's example is not sorted so VLOOKUP might return unpredictable values.

Q12. Does VLOOKUP work differently if tableToSearchIn is sorted in ascending order and I use =VLOOKUP(…;…;…;1) or =VLOOKUP(…;…;…)?
A12. Yes! If you told VLOOKUP that the table is sorted, the function searches the first column for the largest value which does not exceed valueToSearchFor. An exact match is not required. So a search for 3.14159 in the values 1, 2, 3, 4, 5 will find 3 and VLOOKUP will return the corresponding value from columnIdentifier. If you want VLOOKUP to find only exact matches, you must use =VLOOKUP(…;…;…;0).

Q13. I'm using the "sorted" VLOOKUP search, that is =VLOOKUP(…;…;…;1) or =VLOOKUP(…;…;…), and am getting unexpected results. Why?
A13. If you told VLOOKUP that tableToSearchIn is sorted, it is imperative that this is really the case. In this situation, VLOOKUP is allowed to use binary search to locate valueToSearchFor and Calc does not necesssarily begin with the first row of tableToSearchIn and check each successive value. Therefore, if the table isn't sorted you cannot easily predict which row will be located. Either use the exact match search, =VLOOKUP(…;…;…;0), or use Data > Sort on tableToSearchIn to put it in ascending order.

Q14. My tableToSearchIn has numbers in the first column. When I use =VLOOKUP(…;…;…;0) it returns #N/A even though the number is in the table. I have unchecked the Enable regular expressions in formulas option. What's wrong?
A14. A non-empty cell in Calc can hold either a number or text as explained in Ten concepts that every Calc user should know. Numbers and text are different; the number 12 is not the same as the two-character text "12". Since they're different, a search for a numeric valueToSearchFor will not match text values in the first column of tableToSearchIn. If you suspect this is the problem, use View > Value Highlighting so you can tell if the value is numeric (blue) or text (black). Numeric (blue) valueToSearchFor does not match text (black) in tableToSearchIn. In some cases a text (black) valueToSearchFor might match numbers (blue) in tableToSearchIn, but it's risky to rely on that. Either use all numbers or all text for your searches.

Q15. OK, that's the problem (numbers don't match text). How can I fix that?
A15. Select the first column of tableToSearchIn and use the Data > Text to Columns dialog. It can convert numbers to text or text to numbers.

Q16. The "numbers don't match text" difficulty isn't my problem. Both valueToSearchFor and the values in the first column of tableToSearchIn are numbers. What else could be wrong?
A16. VLOOKUP searches based on the values in the cells, not the values that you see on the screen. Those might be different. For example Calc's default is to display only two decimal places in a cell. If the cell's value is 2.99999967 (perhaps the result of a formula) it will display as 3. A search for the cell's value in the table 1, 2, 3, 4, 5 will fail because 2.99999967 isn't found even though it looks as if VLOOKUP is searching for the 3 that you see on the screen.

Q17. My tableToSearchIn has text in the first column. When I use =VLOOKUP(…;…;…;0) it returns #N/A even though that text is in the table. I have unchecked the Enable regular expressions in formulas option. What's wrong?
A17. The matching done by VLOOKUP allows lower case to match upper case and vice versa but does not ignore leading or trailing spaces. So using tutorial's example, a search for "A " (the letter A followed by a space) will fail. It can be difficult to spot problems like that because a cell containing "A" looks just like one containing "A ". Check the lengths of the values in your cells, for example =LEN(B4) and =LEN(F3) should both be 1. You could use Edit > Find & Replace to remove unwanted leading or trailing spaces. See VLOOKUP Retrieves Data from Incorrect Row.

Q18. I have a spreadsheet where someone used 1 for columnIdentifier, that is, the formula is =VLOOKUP(valueToSearchFor;…;1). Isn't that the same as formula =valueToSearchFor, as long as the value is found in the table?
A18. No, because the fourth parameter was omitted and it isn't an "exact match" search; searching for 3.14159 in 1, 2, 3, 4, 5 will return 3 instead of 3.14159.

Q19. The first few VLOOKUPs work but later rows fail. Any ideas?
A19. Yes, check to be sure that you used an absolute reference ($…$…) for the second parameter as shown in the tutorial's example.

Q20. I need to calculate the pay rate based on hours worked: <40→$19  40-45→$19.50  45-50→$20  50-55→$20.50  55+→$21. I am using IF functions =IF(A22<40;19;IF(A22<45;19.5;IF(A22<50;20;IF(A22<55;20.5;21)))) but now I have to add another rate and the formula is getting too complicated. How do I use VLOOKUP to simplify this?
A20. Use an array constant for the second parameter =VLOOKUP(A22;{0;19|40;19.5|45;20|50;20.5|55;21};2;1) instead of nested IF statements. Then you can just adjust the array constant instead of nesting another IF function. See Help for array constant syntax. Some people may prefer HLOOKUP because the hours and pay rates are grouped differently for that function: =HLOOKUP(A22;{0;40;45;50;55|19;19.5;20;20.5;21};2;1).

Q21. I need to differentiate between upper and lower case in tableToSearchIn. VLOOKUP is ignoring the case and finding values I don't want. How do I avoid that problem?
A21. You cannot use VLOOKUP (or LOOKUP or HLOOKUP or MATCH) if you need to differentiate between upper and lower case. It is beyond the scope of this tutorial to discuss ways to solve that problem.

Q22. I need to search a table by two matching valueToSearchFor keys. That is, I want the first key to check column A and the second key to check column B, then return the table entry which exactly matches both of them. How do I do that?
A22. If you want to use VLOOKUP, you will have to create a third column C with the concatenation of the two keys in A and B and use VLOOKUP to search for the concatenation of the two keys in column C. See VLOOKUP across multiple cells. Another approach is described in Searching by rows & columns.

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section. If your VLOOKUP function doesn't work the way you expect, consider that there are many possible difficulties (especially Q5, Q6, Q11, Q13, Q14, Q16, Q17, and Q19). If you're tried the solutions here and are still stumped, when creating a new topic in the Calc section be sure to attach your document (not a picture of it). Otherwise it is hard for the volunteers to help you because in most cases a picture doesn't have enough information to diagnose the problem.
Last edited by MrProgrammer on Sun Apr 14, 2019 6:35 pm, edited 3 times in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] VLOOKUP questions and answers

Post by Villeroy »

Knowing everything about VLOOKUP in unordered "database mode", the ordered "default mode" remains unclear.
=VLOOKUP( valueToSearchFor ; tableToSearchIn ; columnIdentifier ; 0 )
=VLOOKUP( valueToSearchFor ; tableToSearchIn ; columnIdentifier ; 1 )
If the last argument is missing, a default mode 1 is assumed. The two modes serve very different purposes.
The unordered mode returns a value from the first position where valueToSearchFor matches exactly a value in the first column of tableToSearchIn. This mode fetches some value of one distinct list item. It fails with error value #N/A (not availlable) if there is no matching item.
The ordered mode returns a value from the last position where valueToSearchFor is smaller than or equal to a value in the first column of tableToSearchIn. This mode fetches a closest match that falls into some range of values on an ordered scale. It fails with error value #N/A if the search value is smaller than the smallest list item.
The attached file has 3 sheets. The first one demonstrates the default mode for all lookup functions LOOKUP, VLOOKUP and MATCH. LOOKUP supports ordered mode only.
The second sheet demonstrates a 3rd descending ordered mode that can be used with MATCH.
The third sheet tries to explain why ordered mode requires sorted lists. It demonstrates what happens when the list is not properly sorted.
Attachments
ordered_scale_lookup.ods
Demonstrating ordered scale lookup
(27.8 KiB) Downloaded 2174 times
Last edited by Villeroy on Sat Dec 08, 2012 9:24 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Tutorial] VLOOKUP questions and answers

Post by MrProgrammer »

Q23. What happens if I use=VLOOKUP(…;…;…) or =VLOOKUP(…;…;…;1) but the tableToSearchIn isn't in ascending order?
A23: As explained in A13 above, VLOOKUP would be allowed to use a binary search to locate valueToSearchFor so the simple answer, for non-programmers, is that you will get unpredictable results and your spreadsheet will produce incorrect or misleading calculations or other garbage. The Calc documentation doesn't explain what happens if the table entries are not in ascending order so I ran some experiments. The attached spreadsheet shows tests that were run with OOo 3.2 and the "Excel compatibility" settings":
  • Search criteria = and <> must apply to whole cells checked and
  • Enable regular expressions in formulas unchecked
The tests show that =VLOOKUP(…;…;…;1) does not scan tableToSearchIn sequentially to locate the last value which does not exceed valueToSearchFor. Instead, VLOOKUP checks the middle row of the table to determine if it should continue the search in the first half or the last half of the table. So if tableToSearchIn is $A$2:$B$34, the middle is row (2+34)/2 and VLOOKUP compares cell $A$18 with valueToSearchFor. If $A$18 is smaller, VLOOKUP assumes that valueToSearchFor cannot be anywhere in the second half of the table and checks the middle of the first half of the table, row (2+17)/2. Row 9½ doesn't exist so VLOOKUP compares cell $A$9 with valueToSearchFor to determine if valueToSearchFor is in the first quarter or the second quarter of the table. This process locates the result much more quickly than a sequential scan but means that it is effectively impossible to predict which element will be located unless the table is in ascending order. Any element which is out of order could produce an unexpected result from VLOOKUP. It's possible that other versions of AOO or OOo or LO or NeoOffice or other derivatives may use different algorithms. In any case, using =VLOOKUP(…;…;…) or =VLOOKUP(…;…;…;1) with an unsorted tableToSearchIn is going to be a bad idea.
201204281443.ods
(12.64 KiB) Downloaded 1896 times
Q24. Why am I getting Err:502 from VLOOKUP?
A24: The most likely problem is that columnIdentifier is larger than the number of columns that you specified for tableToSearchIn. For example if columnIdentifier is 4 then tableToSearchIn must specify at least four columns.

Q25. What happens when VLOOKUP finds valueToSearchFor in tableToSearchIn but the corresponding cell in columnIdentifier is empty?
A25: Let's start with the other cases first. If the corresponding cell has data, that data is returned, whether it's numeric or text. If the corresponding cell has an error like #NUM! or #N/A, that error is returned. If the corresponding cell contains a formula, the formula's value is returned, whether it's a number, text, or an error, and even if the value is a text string of length 0. You can use the ISNUMBER(), ISTEXT(), and ISERROR() functions to determine what kind of value has been returned by VLOOKUP. In all cases, the VLOOKUP formula's cell will test FALSE for ISBLANK() because the cell contains a formula.

This leaves the case where the corresponding cell in columnIdentifier is empty — no data and no formula. In this situation:
  • If option Precision as shown is unchecked, an empty value is returned. The VLOOKUP formula's cell will test FALSE for ISBLANK() [since the cell contains a formula], FALSE for ISNUMBER() [since the value is not numeric], and FALSE for ISTEXT() [since the value is not text]. No data will appear in the cell.
  • If option Precision as shown is checked, a numeric zero is returned! The VLOOKUP formula's cell will test FALSE for ISBLANK() [since the cell contains a formula], TRUE for ISNUMBER() [since the value is zero], and FALSE for ISTEXT() [since the value is not text]. 0 will appear in the cell.
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
jvz1
Posts: 2
Joined: Mon Jan 18, 2016 3:56 pm

Re: [Tutorial] VLOOKUP questions and answers

Post by jvz1 »

When regular expressions are enabled for a Calc document, VLOOKUP and related functions are impacted. There is quite a big chance that the function won't work as one initially would expect. The SearchFor value for example in the VLOOKUP function is interpreted as a regular expression. In regular expressions, character like .^$(){}[], have a special meaning (in general they match/select multiple characters) and won't be used to match literal text in the SearchIn table. In order to match for literal text, with regular expression enabled, the SearchFor value will need to be escaped.

This can be achieved using a simple function like EscapeRegex below. How to use this function is explained in the comments above the function.

Code: Select all

' Escape special characters used in regular expressions. The list of special charcters was
' fetched from: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Writer.
'
' Use this function in Calc functions like VLOOKUP when SearchFor value contains special:
'
' =VLOOKUP(EscapeRegex(A1);$C1$E10;1;0)
'
' As an alternative to fully disable regular expressions for the entire calc sheet using OpenOffice
' Calc Document settings. 
'
' See also: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Calc#Regular_expressions_in_Calc_functions
Function EscapeRegex( Value As String )

    FunctionAccess = createunoservice("com.sun.star.sheet.FunctionAccess")
    SpecialChars   = ".^$*+?\[](){}|"
    
    n    = 1
    Char = Mid( SpecialChars, n, 1 )
    
    While Char <> ""
    
        Value = FunctionAccess.callFunction( "SUBSTITUTE", Array( Value, Char, "\" + Char ) ) 
        
        n     = n + 1
        Char  = Mid( SpecialChars, n, 1 )
        
    Wend
    
	EscapeRegex = Value
    
End Function
LibreOffice 5.0.6.2 on Ubuntu 15.10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Tutorial] VLOOKUP questions and answers

Post by MrProgrammer »

Q26. What happens when valueToSearchFor is the address of an empty cell?
A26: The numeric value of an empty cell is zero, so VLOOKUP will try to find the value 0 in tableToSearchIn and return the corresponding data from columnIdentifier. If 0 cannot be located in the table, #N/A is returned. If the left column of tableToSearchIn contains text values instead of numbers, it may be more appropriate to use the T() function, that is, =VLOOKUP(T(cell);…;…;…) since T will return an empty string when the cell is empty. However you will need to put formula ="" in the left column of tableToSearchIn because VLOOKUP does not match an empty string with an empty cell.

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Locked