### [Tutorial] VLOOKUP questions and answers

Posted:

**Mon Jan 09, 2012 9:20 pm**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.

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:

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(…;…;…;…));"";VLOOKUP(…;…;…;…)). Or put =VLOOKUP(…;…;…;…) in an offscreen column (say ZA2) and use =IF(ISNA(ZA2);"";ZA2) in the main part of your spreadsheet. This means:

A3. You can't put a default in the table, but you can put a default in the formula. Use =IF(ISNA(VLOOKUP(…;…;…;…));defaultValue;VLOOKUP(…;…;…;…)) 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 Match Formula.

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.

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.

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

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(…;…;…;…));"";VLOOKUP(…;…;…;…)). Or put =VLOOKUP(…;…;…;…) 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.

A3. You can't put a default in the table, but you can put a default in the formula. Use =IF(ISNA(VLOOKUP(…;…;…;…));defaultValue;VLOOKUP(…;…;…;…)) 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 Match Formula.

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.