Excel Excellence: Navigating Data with LOOKUP Functions

These functions encompass different types, each designed for a specific use. The most common LOOKUP functions are:

  • VLOOKUP()

  • HLOOKUP()

  • INDEX() and MATCH()

VLOOKUP() FUNCTION

VLOOKUP() or Vertical Lookup() is used to search for a value in the first column of a table and retrieve a corresponding value from another column. VLOOKUP is useful for quickly finding information in a structured table.

The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

lookup_value = the value to look for (mostly the primary key in the table) table array = the table range col_index_num = the column number to retrieve the data from range _lookup = whether to perform an exact match (False) or an approximate match (True)

Let us look at an example to understand the VLOOKUP() function better.

This Excel sheet contains the entire data about the employees in an organization.

Only the employee ID and First Name are in this sheet, but we need to know the Last Name, Department, and Pay Rate.

We apply VLOOKUP() for the last name by opening the function argument console.

The look_up value is the first employee ID, the table array consists of the required columns of the Master Emp List (here, required till the last name), the column index is 2 since the last name falls in the 2nd column and the Range_lookup is ‘FALSE’ as we can get an exact match.

Here we go! We got the Last Name of our employees.

We can get the Department and Pay rate values by using a similar function and changing the arguments accordingly.

HLOOKUP() FUNCTION

HLOOKUP() stands for Horizontal Lookup. It functions similarly to the VLOOKUP() but with a difference. It searches **horizontally **for a value in the first row of a table and retrieves a corresponding value from a specified row.

The syntax is HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Below are the two worksheet examples we are considering.

From the other worksheet with all the data, we must determine the Warehouse 2 and 3 Inventory values in the Inventory Status Check spreadsheet.

The formula looks as given below:

The lookup_value should be an absolute value as it remains the same for all the warehouse inventory — also, the row index number changes for each inventory as the specified value is surfed horizontally. For instance, the row index number for Warehouse 2 Inventory, and Warehouse 3 Inventory is 2, 3 respectively.

We get this result when we apply the above formula.

INDEX and MATCH() FUNCTION

  • The Index() returns the value at a specific position. Syntax: INDEX(array, row_num, [column_num]).

  • The **Match() **returns the numeric position of a value. Syntax: MATCH(lookup_value, lookup_array, [match_type]).

VLOOKUP() and HLOOKUP() always search for the lookup value in the leftmost column of the table and retrieve data from the right. It doesn’t support right-to-left lookups. In contrast, INDEX() and MATCH() can overcome this limitation.

In the below image, we can see that the Emp ID which acts as the lookup value is placed somewhere in the middle of the data.

We need to find the department of the given emp ID.

Below is the INDEX() formula that should be given.

We are giving the MATCH() function for the Row_num as it helps to retrieve the position of the value we are searching for.

For the Match_type,

  • We have given 0 as it finds the first value that is exactly equal to the lookup value.

  • 1 finds the largest value that is less than or equal to the lookup value.

  • -1 finds the smallest value that is greater than or equal to the lookup value.

In the INDEX() function argument, column_num is not needed as our array has only one value of data.

We get the department name for the given Emp ID even when the column is not placed as the first on the left side of the table.

Hence, in the dynamic landscape of data analysis and spreadsheet administration, acquiring expertise in lookup functions is akin to unlocking a powerful set of tools within Excel. By incorporating these functions into our skill set, we’ll find ourselves operating with greater efficiency, drawing more insightful conclusions, and ultimately elevating the effectiveness of our work in the realms of data analysis and spreadsheet management.