If you are planning to step up your proficiency in Excel, you should learn how to use the look up functions. These functions are commonly used on tables that act like a database. While databases have SQL queries, which is challenging especially for non-programmers, Excel has these simple look up functions and the most widely used are VLOOKUP, HLOOKUP and LOOKUP.
The VLOOKUP stands for vertical look up. This function looks for a value in the leftmost column of a table, and then returns the value in the same row from another column you specify.
Step 1: Insert VLOOKUP function
You can directly type =VLOOKUP in the address bar or you can click on Formulas > Insert Function then type VLOOKUP in search textbox. Click the GO button or press enter to filter the list of functions.
This VLOOKUP function will be shown in the list with its description below.
Click OK button.
Step 2: Provide arguments
VLOOKUP accepts four arguments. The syntax is:
VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)
- Lookup_value is the value to be found in first column of a table and can be a value, a reference or a text string.
- Table_array is a table of text, numbers, or logical values, in which data is retrieved.
- Col_index_num is the column number in a table array from which the matching value should be returned. The first column of values in the table is column 1.
- Range_lookup is either Approximate match (TRUE) or Exact match(FALSE). Approximate match will find the closest match in first column and Exact match will only find the exact match. If this is omitted, the default value will be TRUE.
In this example, the Lookup_value is B4, Table_array is K3:L53, Col_index_num is 2 and Range_lookup is FALSE.
Click OK and check the result.
What happens here is that the VLOOKUP function looks for the Abbreviation (AL) in the leftmost column of the range $K$3:$L$53 and returns the value in the same row from the second column because the third argument is set to 2. The fourth argument is set to FALSE so it returns the exact match.
Drag the VLOOKUP function in cell C4 down to cell C18. When we drag the VLOOKUP function down, the absolute reference ($K$3:$L$53) stays the same because we included a dollar sign, while the relative reference (C4) changes to C5, C6, C7, etc.
If we have a vertical look up, we should also need a horizontal look up. The HLOOKUP function looks for a value in the topmost row of a table, and then returns a value in the same column from the row you specify. This is very much the same with the VLOOKUP and it has the same procedure of inserting and defining its arguments.
The syntax is:
HLOOKUP(Lookup_value, Table_array, Row_index_num, Range_lookup)
This function looks up a value either from one row or one column range or from an array. This is an old function but still included in newer versions of Excel for backward compatibility. It may be less useful than VLOOKUP and HLOOKUP but you may find it simpler to use for your particular task.
Step 1: Insert the LOOKUP function
The syntax of the function is:
Syntax: LOOKUP(Lookup_value, Lookup_vector, [Result_vector])
- Lookup_value is a value that LOOKUP searches for in the first vector. This can be a number, text, a logical value, or a name or reference that refers to a value.
- Lookup_vector is a range that contains only one row or one column. The values can be text, numbers, or logical values.
- Result_vector is a range that contains only one row or column. The result_vector argument must be the same size as lookup_vector and is optional.
You can download a sample workbook so you can explore the look up functions.
|Download Sample Workbook Here|
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment