Master Excel Lookup Function

Microsoft Excel offers a range of functions to simplify data analysis, including the HLOOKUP, VLOOKUP, XLOOKUP,  function. This article will cover these 3 lookup functions in detail, discuss a few of its differences and provide practical examples to help you fully harness its potential. Whether you are a beginner or an experienced data analyst, understanding how to use lookup functions can greatly enhance your excel skills.

HLOOKUP

HLOOKUP stands for Horizontal Lookup. It is used to search for a value in the top row of a table or range and returns a value in the same column from a specified row. HLOOKUP is particularly useful when dealing with data formatted in rows rather than columns.

Syntax
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: The value to search for in the first row of the table_array.
• table_array: The range of cells that contains the data. You can include multiple rows but only one column.
• row_index_num: The row number in the table_array to retrieve a value.
• range_lookup: A logical value that specifies whether to find an exact match (FALSE) or an approximate match (TRUE). If omitted, the default is TRUE.

Using HLOOKUP

• Click on the cell where you want the result from the HLOOKUP function to appear. Type ‘=HLOOKUP’ ( to start your formula)
• Next is the lookup_value.The lookup_value is the value HLOOKUP searches for in the first row of your table array. This could be typed directly into the formula, such as “John” or referenced from another cell. For Example =HLOOKUP(“June”,… or HLOOKUP(A1,… where A1 contains the name “John”
• The table_array is the range of cells(Table) that contains the data. It’s crucial to include the full range of where the data is located. For example: =HLOOKUP(“June”, A1:M3,… ‘A1:M3’ is the range where A1:M1 includes the names, and A2:M3 contains the data you need to retrieve.
• The row_index_num is the row number in table_array to retrieve a value. This number should be greater than or equal to 1. For example, if your lookup value(the value used to reference the search) is in the first row of your range and you want to search for data from the second row, you would use: =HLOOKUP(“June”, A1:M3, 2,… 
• The range_lookup is a boolean value (TRUE or FALSE) that specifies whether you seek an exact match (FALSE) or an approximate match (TRUE). Although optional, if omitted, Excel assumes  approximate match(TRUE). For exact matches, it is especially useful when the lookup values are categorical or textual: =HLOOKUP(“June”, A1:M3, 2, FALSE)
• Finish up by closing parenthesis and pressing enter.

For Examples:
Suppose you have a dataset with monthly sales data, with months listed horizontally, and you want to find the sales in June.
=HLOOKUP(“June”, A1:M2, 2, FALSE)

Unsure of the exact name or formatting of the lookup value, make use of wildcards (* or ?):
=HLOOKUP(“Jun*”, A1:M2, 2, FALSE)

VLOOKUP

VLOOKUP stands for Vertical Lookup. The Excel VLOOKUP function is used to retrieve information from a table using a lookup value.VLOOKUP can only look to the right. In other words, you can only retrieve data to the right of the first column in the table provided to VLOOKUP. It supports approximate and exact matching, and wildcards (* ?) for partial matches.

Syntax
=VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])

• lookup_value – The value to look for in the first column of a table.
• table_array – The table from which to retrieve a value.
• column_index_num – The column in the table from which to retrieve a value.
• range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

Using VLOOKUP

• Click on the cell where you want the result from the VLOOKUP function to appear. Type ‘=VLOOKUP’ ( to start your formula)
• Next is the lookup_value.The lookup_value is the value VLOOKUP searches for in your table array. This could be typed directly into the formula, such as “Canada” or referenced from another cell. For Example =VLOOKUP(“June”,… or VLOOKUP(A1,… where A1 contains the name “Canada”

• The table_array is the range of cells(Table) that contains the data. It’s crucial to include the full range of where the data is located. For example: =VLOOKUP(“Canada”, A1:J5,…
• The column_index_num is the index number in table_array to retrieve a value. This number should be greater than or equal to 1. 
• The range_lookup is a boolean value (TRUE or FALSE) that specifies whether you seek an exact match (FALSE) or an approximate match (TRUE). If omitted, Excel assumes an approximate match(TRUE).
• Finish up by closing parenthesis and pressing enter.

Examples

To look up information by ID in the table below, we must provide the range D3:F9 as the table, and that means we can only look up Email and Department (because VLOOKUP only looks to the right).

XLOOKUP

The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP.  XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges.

Syntax
=XLOOKUP(lookup,lookup_array,return_array,[not_found],[match_mode],[search_mode])

• lookup – The lookup value.
• lookup_array – The array or range to search.
• return_array – The array or range to return.
• not_found – [optional] Value to return if no match found.
• match_mode – [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
• search_mode – [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

Examples

By default, XLOOKUP will perform an exact match. The example below makes use of the XLOOKUP function to search for the diameter of mars (cell F4) in the table (B4:B12) and returns the value to the active cell.

Reasons Why Lookup Function Might Not Be Working

Several reasons can cause your function to fail and throw errors :

• The lookup_value does not exist in the first row of the table_array.
• The row_index_num is less than 1 or greater than the number of rows in the table_array.
• There are inconsistencies or errors in data types (e.g., text vs. number).
• Incorrect usage of the range_lookup argument causes unexpected results.

Differences Between VLOOKUP and HLOOKUP