Common errorsĪs you learn how to use VLOOKUP you will come across a number of common error messages. To Excel, numbers and text are different and therefore will not match. Where numbers are stored as text a lookup_value may not be found, even though it appears it should.When using ‘FALSE’ the first column of the table_array does not need to be sorted. Only set the range_lookup to ‘TRUE’ when the first column of the table_array is sorted.If you require a lookup based on a horizontal list use the HLOOKUP function.
VLOOKUP only works when the data is organized as a vertical list.Where there are multiple instances of the lookup_value in the table_array, only the value associated with the first instance will be returned.the first column included in the table_array) VLOOKUP can only look up in the leftmost column of the data (i.e.If you wanted to copy the function to other cells remember to include a $ symbol in front of the row/column references to ‘freeze’ the cell references on the correct cells. It is possible to include any of the arguments as cell references.īased on the cell references above, our syntax could be amended: =VLOOKUP(F2,A2:C9,F3,FALSE) In our example, our formula would be: =VLOOKUP("O Reading",A2:C9,3,FALSE) Until you understand how to use this correctly, it is best to use FALSE. Where the value is omitted the function will default to ‘TRUE’. ‘FALSE’ tells the VLOOKUP function to find an exact match, ‘TRUE’ tells the VLOOKUP function to find the nearest value that is still less than the lookup_value. If we wanted to retrieve the Department we would use 2, as Department is the 2nd column of the table_array). col_index_num the column in the table from which to retrieve the value (3 in our example, as it is the 3rd column of the table_array.table_array the range of cells which contains the data (A2 C9 in our example).lookup_value the value to lookup in the first column (“O Reading” in our example).The Syntax of the function is: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) VLOOKUP does exactly the same thing as we would do in real life. If we wanted to find the Telephone number for ‘O Reading’ in real life we would look down column A until we found the name, then we would move across to column C (the 3rd column) to find the Telephone number.
To really understand VLOOKUP it would be beneficial to use an example. The purpose of this post is to teach you how to use VLOOKUP. Personally, I think it is because VLOOKUP is probably the most advanced function the interviewer knows! It is certainly not the most advanced function, but you need to know it if you want to use Excel to any reasonable standard. It is used in job interviews all over the world to understand a person’s skills with Excel. You could use VLOOKUP to find the telephone number of any employee based on their name.īeing able to use VLOOKUP correctly is an Excel ability benchmark. A simple example would be a where you had a long list of employees and their telephone numbers. It allows you to look up a value from a table based on a search term. VLOOKUP is one of the most powerful functions in Excel. You’ll learn about dynamic arrays later, in: Session Four: Working with Dynamic Arrays. Unless you need to share your workbooks with users of Excel 2019 and earlier, you should use XLOOKUP for all new work. XLOOKUP also replaces HLOOKUP as it is able to lookup both horizontally and vertically. In: Lesson 3‑22: Use a VLOOKUP function for an exact lookup (sidebar), you learned that the new XLOOKUP dynamic array function has now completely replaced VLOOKUP. You’ll see the VLOOKUP function used far more often than HLOOKUP but you may find a scenario where it is useful. This means that you can search for a matching value in a (horizontal) row and return a value from any row in that column. In the HLOOKUP function, H is an abbreviation of Horizontal.
This means that you search for a matching value in a (vertical) column and return a value from any column in that row. In the VLOOKUP function, V is an abbreviation of Vertical.