We're already familiar with the VLOOKUP function, often used to solve lookup tasks. Until now, we've only applied it for exact matches. Let's explore how it can help us with other types of problems as well!

Our table keeps records of a company's employees. We can see their names, employee IDs, departments, the exact date they started working at the company, and based on this, how many years they've been employed there. The values in column E represent full years only; partial years are not counted in the employment duration. We'd like to classify employees into appropriate categories based on the length of their employment. These categories are shown in the small helper table.

We need to examine the values in column E. If a number falls between 0 and 5, it belongs to category A; if between 6 and 10, then category B; if between 11 and 15, category C; and if it's 16 or above, category D. While this could be solved using several nested IF functions, there's a simpler and shorter solution—the VLOOKUP function!

Let's examine how exactly this version of VLOOKUP works. Our lookup value will be the first value to classify from column E. We'll search for it in the first column of the helper table, scanning from top to bottom. However, this exact value won't be present in the helper table, so the VLOOKUP function won't find an exact match—but that's perfectly fine. Instead, it will stop at the closest lower value in the helper table. For instance, with the lookup value of 7, it will find 6 as the closest lower match, classifying the value 7 into category B.

In general terms, this version of VLOOKUP works by stopping at the largest value that is less than or equal to the lookup value.

Therefore, it's very important that the helper table includes the lower bounds of each interval sorted in ascending order. Although our helper table also lists the upper bounds, the formula itself does not require these upper-limit values.

Let's insert the VLOOKUP function into cell F4. The lookup value is the number found in cell E4. The table array is the range containing the threshold values and categories from the helper table, and the column index is 3, since we expect the result from the third column of the previously selected range.

In the range_lookup field, we'll now enter 1, meaning the function will perform an approximate match rather than an exact one.

Some employees had to take exams on two different topics. The points they earned are listed in columns B and C, with 50 as the maximum possible score for each. Column D shows the total points earned. Based on this total score, we want to classify employees into the 5 categories defined in the helper table.

This task is very similar to the previous one; we'll again use the VLOOKUP function. Let's insert it into cell E2. The lookup value will be the first total score in column D, the table array is the range containing threshold values and their corresponding categories, and the column index is 3 again. In the final field, enter 1, since we're performing an approximate match. Just like before, the function stops searching at the closest value that is equal to or less than the lookup value.

We typically use the VLOOKUP function to solve lookup tasks. However, with VLOOKUP, we can only search based on a single column. Let's see what we can do if we have multiple conditions to consider.

On this worksheet, we have data about orders, such as order ID, date, customer, ordered products, and their colors. Products are available in multiple colors, and the price can vary depending on the color. The exact prices are listed on the Price List worksheet.

Our task is to display the unit prices in column F. We can still use the VLOOKUP function, but since we want to search based on two columns (product and color), we need to combine these columns into one. We can concatenate the values of the two columns by using functions like CONCAT, TEXTJOIN, or simply the & operator. Let's use the & operator for this task.

Let's perform the concatenation in both worksheets, each in a new column. Once that's complete, insert the VLOOKUP function into cell F2 and specify its arguments as follows. The lookup value will be cell H2 from our current worksheet, while the table array is the range A2:D13 from the Price List worksheet. Remember to make this reference absolute. The column index number is 4, and we'll set the range lookup to FALSE since we're performing an exact search. After that, copy the formula down to complete the task!

We can also solve this problem using another function, namely SUMIFS. Although this function isn't classified among the lookup functions, it can still serve as one in certain scenarios. SUMIFS returns the sum of numbers from specified columns that meet given criteria. If only a single row meets these criteria, the result is simply the sum of a single number—which, of course, equals the number itself. However, be cautious: because of this characteristic, you can only use this function in cases where the returned value is numeric! Insert a new column before column G, and then insert the SUMIFS function.

The sum range will be the range on the Price List worksheet containing the values we want as the result—this is the column from which the function will sum values, resulting in a single number. Select column D from the Price List worksheet!

The first criteria range will be the column containing products from the Price List worksheet, and its corresponding criteria is the first product to look up from the Orders worksheet

The second criteria range is the column containing colors from the Price List, with its matching criteria being cell E2.

Finally, copy the formula down!