Calc spreadsheet file used in video: Right-click to download file
VLOOKUP stands for vertical lookup. It searches vertically down the left most column of an array of values for an amount and returns a value in another column to the right. The array must contain at least 2 columns
if VLOOKUP is set to search through a column of unsorted values, it will search vertically down the first column for a value, then return another value on the same row in a specified column. If the value is not found it returns an N/A error.
If it's set to search a column which is sorted in ascending order, it will search down the column for a match, if it finds an exact match, it will return the value on the same row in a specified column. If it reaches a value greater than the value being looked up, it will jump up one row to the next lower value and return a value in a specified column of that row. If it reaches the last row of the column and doesn't find a match it will return the value in the specified column of the last row.
The parameters for VLOOKUP are:
For the first example A drop-down is created in a cell to select a product from a list. This will avoid input errors that might be made if the user typed a product in the cell:
Or you can use the shortcut: hold down Alt and press d v
Now when you click the arrow to the right of the dropdown box, you can choose a value.
I want to get the price of the product I choose in the drop-down:
Now when you change the selection in the dropdown, it will display the price of the product. Without a dropdown the user could type in a product which isn't on the list. This would return an N/A error.
If your looking up a value in a table sorted in ascending order, where an exact match isn't nessesary, you would use 1 as the final parameter.
You might use this sort of lookup in a table of commisions.
If $6000 is typed into the amount cell, it's larger than the highest amount in column A so it returns the commission on the last row 10.0%.
If you type $1000 which is an exact match, it will return the commission on the same row 5.0%.
If $999 is typed into the amount cell, it will search down column A and when it finds $1000, it will jump up one row to the next lowest and return 1.0%.
HLOOKUP stands for horizontal lookup, it works exactly like VLOOKUP except it searches horizontally across the topmost row of an array for a value and returns a value in another row.
In the following example, a drop-down has been created in B6 which dispays a list of products in row 1.
Tables are not usually arranged this way, so it's rare that you would use HLOOKUP.