Excel is a powerful tool for working with data, but sometimes you may need to perform a task that is not straightforward with the built-in functions. One such task is to look up a value in a column and put a different value in a cell in the same row, based on some criteria. For example, you may want to assign a category to each product based on its price, or update the status of an order based on its delivery date.
There are different ways to achieve this goal in Excel, depending on the complexity and structure of your data. In this article, we will explore some of the most common methods, such as using VLOOKUP, INDEX and MATCH, or IF and IFS functions. We will also explain the basic theory behind each method, and provide a step-by-step guide on how to use them. Finally, we will give a detailed example with real data, and show the results of each method.
Before we dive into the methods, let’s review some basic concepts that are essential for understanding how they work.
Lookup Value
The lookup value is the value that you want to find in a column. It can be a number, text, date, or any other type of data that can be stored in a cell. For example, if you want to find the price of a product, the product name or code can be the lookup value.
Lookup Column
The lookup column is the column that contains the lookup values. It is also called the key column, because it is used to identify each row in the table. The lookup column should be unique, meaning that each value in the column should appear only once. For example, if you have a table of products, the product code can be the lookup column.
Return Column
The return column is the column that contains the values that you want to put in the cells in the same row as the lookup values. It is also called the value column, because it is used to provide the output of the lookup function. The return column can be any column in the table, as long as it has the same number of rows as the lookup column. For example, if you have a table of products, the price or category can be the return column.
Lookup Range
The lookup range is the range of cells that contains both the lookup column and the return column. It is also called the table array, because it is used to define the source of the lookup function. The lookup range can be any range of cells in the worksheet, as long as it includes the lookup column and the return column. For example, if you have a table of products in the range A2:C10, where column A is the product code, column B is the price, and column C is the category, the lookup range can be A2:C10.
Lookup Function
The lookup function is the function that performs the lookup operation, by taking the lookup value, the lookup range, and the return column as arguments, and returning the corresponding value from the return column. There are different lookup functions in Excel, such as VLOOKUP, HLOOKUP, LOOKUP, INDEX and MATCH, XLOOKUP, and XMATCH. Each function has its own syntax and features, which we will discuss in the next section.
Methods
In this section, we will explain how to use some of the most common lookup functions in Excel, and compare their advantages and disadvantages.
VLOOKUP
VLOOKUP is one of the most popular lookup functions in Excel. It stands for Vertical Lookup, because it searches for the lookup value in the first column of the lookup range, and returns the value from the return column in the same row. The syntax of VLOOKUP is:
=VLOOKUP(lookup_value, lookup_range, return_column, match_type)
where:
- lookup_value is the value that you want to find in the first column of the lookup range.
- lookup_range is the range of cells that contains both the lookup column and the return column.
- return_column is the number of the column in the lookup range that contains the values that you want to return. The first column is 1, the second column is 2, and so on.
- match_type is an optional argument that specifies how to match the lookup value with the values in the lookup column. It can be either TRUE or FALSE, or 1 or 0. If you enter TRUE or 1, or leave the argument blank, the function returns an approximate match, meaning that it will find the closest value that is less than or equal to the lookup value. If you enter FALSE or 0, the function returns an exact match, meaning that it will find the exact value that is equal to the lookup value. If no match is found, the function returns an error.
The advantages of VLOOKUP are:
- It is easy to use and understand, especially for beginners.
- It can handle both exact and approximate matches, depending on the match_type argument.
- It can work with any type of data, such as numbers, text, dates, or logical values.
The disadvantages of VLOOKUP are:
- It can only look up values in the first column of the lookup range, and return values from the columns to the right. If you want to look up values in a different column, or return values from the columns to the left, you need to rearrange your data or use a different function.
- It can be slow and inefficient, especially if you have a large lookup range or multiple VLOOKUP formulas in your worksheet. This is because the function scans the entire lookup range for each lookup value, even if it finds a match in the first row.
- It can be prone to errors, especially if you insert or delete columns in your lookup range. This is because the function uses the return_column argument as a relative reference, meaning that it counts the number of columns from the first column of the lookup range. If you change the structure of your lookup range, you need to update the return_column argument accordingly, or use an absolute reference, such as $C$2:$E$10.
INDEX and MATCH
INDEX and MATCH are two separate functions that can be combined to perform a lookup operation. INDEX returns the value of a cell in a range, based on its row and column position. MATCH returns the position of a value in a range, based on a match type. The syntax of INDEX is:
=INDEX(range, row_num, col_num)
where:
- range is the range of cells that contains the values that you want to return.
- row_num is the number of the row in the range that contains the value that you want to return. The first row is 1, the second row is 2, and so on.
- col_num is the number of the column in the range that contains the value that you want to return. The first column is 1, the second column is 2, and so on.
The syntax of MATCH is:
=MATCH(lookup_value, lookup_range, match_type)
where:
- lookup_value is the value that you want to find in the lookup range.
- lookup_range is the range of cells that contains the values that you want to match with the lookup value. It can be either a single row or a single column.
- match_type is an optional argument that specifies how to match the lookup value with the values in the lookup range. It can be either -1, 0, or 1. If you enter -1, the function returns the largest value that is less than or equal to the lookup value. If you enter 0, the function returns the exact value that is equal to the lookup value. If you enter 1, the function returns the smallest value that is greater than or equal to the lookup value. If no match is found, the function returns an error.
To use INDEX and MATCH together, you can nest the MATCH function inside the INDEX function, and use the output of the MATCH function as the row_num or col_num argument of the INDEX function. For example, to look up a value in column A and return the value from column C in the same row, you can use the following formula:
=INDEX(C2:C10, MATCH(B7, A2:A10, 0))
where:
- C2:C10 is the range that contains the values that you want to return.
- B7 is the lookup value that you want to find in column A.
- A2:A10 is the lookup range that contains the values that you want to match with the lookup value.
- 0 is the match type that specifies an exact match.
The advantages of INDEX and MATCH are:
- They can look up values in any column of the lookup range, and return values from any column in the same range. You do not need to rearrange your data or use a different function.
- They can be faster and more efficient, especially if you have a large lookup range or multiple lookup formulas in your worksheet. This is because the MATCH function only scans the lookup column for each lookup value, and the INDEX function only returns the value from the return column, without scanning the entire lookup range.
- They can be more flexible and dynamic, especially if you insert or delete columns in your lookup range. This is because the MATCH function uses the lookup_range argument as an absolute reference, meaning that it does not change if you change the structure of your lookup range. You do not need to update the lookup_range argument accordingly, or use a relative reference, such as C2:C10.
The disadvantages of INDEX and MATCH are:
- They can be more complex and difficult tounderstand, especially for beginners. You need to use two functions instead of one, and remember the syntax and arguments of each function.
- They can only handle exact matches, depending on the match_type argument. If you want to perform an approximate match, you need to sort your lookup range in ascending or descending order, and use the appropriate match_type argument. Otherwise, the function may return an incorrect or unexpected result.
IF and IFS
IF and IFS are two logical functions that can be used to perform a lookup operation, by testing a condition and returning a value based on the result. IF returns a value based on a single condition. IFS returns a value based on multiple conditions. The syntax of IF is:
=IF(logical_test, value_if_true, value_if_false)
where:
- logical_test is the condition that you want to test. It can be any expression that returns a TRUE or FALSE result, such as A2=“Apple” or B2>100.
- value_if_true is the value that you want to return if the logical_test is TRUE. It can be a number, text, date, logical value, cell reference, formula, or another IF function.
- value_if_false is the value that you want to return if the logical_test is FALSE. It can be any of the same types as value_if_true, or blank.
The syntax of IFS is:
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …)
where:
- logical_test1, logical_test2, … are the conditions that you want to test, in the order of priority. They can be any expressions that return a TRUE or FALSE result, such as A2=“Apple” or B2>100.
- value_if_true1, value_if_true2, … are the values that you want to return if the corresponding logical_test is TRUE. They can be any of the same types as value_if_true in the IF function, or blank.
To use IF and IFS to perform a lookup operation, you can use the lookup value as the logical_test argument, and the return value as the value_if_true argument, for each possible value in the lookup column. For example, to look up a value in column A and return the value from column C in the same row, based on a predefined category, you can use the following formula:
=IFS(A2=“Apple”, “Fruit”, A2=“Banana”, “Fruit”, A2=“Carrot”, “Vegetable”, A2=“Donut”, “Dessert”, TRUE, “Unknown”)
where:
- A2 is the lookup value that you want to find in column A.
- “Fruit”, “Vegetable”, “Dessert”, and “Unknown” are the values that you want to return from column C, based on the category that you have defined for each product.
- TRUE is the default condition that applies to any value that does not match any of the previous conditions.
The advantages of IF and IFS are:
- They can look up values in any column of the lookup range, and return values from any column in the same range. You do not need to rearrange your data or use a different function.
- They can be more intuitive and easy to understand, especially for beginners. You can use plain English to define the conditions and the values that you want to return.
- They can handle both exact and approximate matches, depending on how you define the conditions. You can use operators such as =, <, >, <=, >=, <>, and & to compare the lookup value with the values in the lookup column.
The disadvantages of IF and IFS are:
- They can be very long and complex, especially if you have many possible values in the lookup column. You need to write a condition and a value for each value, and make sure that they are in the correct order and syntax.
- They can be inefficient and error-prone, especially if you have a large lookup range or multiple lookup formulas in your worksheet. This is because the function evaluates each condition until it finds a match, or until it reaches the end of the arguments. If you have a lot of conditions, the function may take a long time to calculate, or return an incorrect or unexpected result.
- They can be hard to maintain and update, especially if you change the values or the categories in your lookup range. You need to modify the formula accordingly, or use a reference table, such as a named range or a separate worksheet, to store the values and the categories.
Example
To illustrate how to use the methods that we have discussed, let’s look at an example with real data. Suppose you have a table of products and their prices in the range A2:B10, as shown below:
TableProduct Price Apple 1.5 Banana 2 Carrot 0.8 Donut 3 Egg 1.2 Fish 5 Grape 2.5 Honey 4 Ice 0.5 You want to look up the price of a product in column A, and put a category in column C, based on the following criteria:
- If the price is less than 1, the category is “Cheap”.
- If the price is between 1 and 3, the category is “Moderate”.
- If the price is greater than 3, the category is “Expensive”.
To do this, you can use any of the methods that we have discussed, and enter the formula in cell C2, and copy it down to the rest of the column. The results are shown below:
TableProduct Price Category Apple 1.5 Moderate Banana 2 Moderate Carrot 0.8 Cheap Donut 3 Moderate Egg 1.2 Moderate Fish 5 Expensive Grape 2.5 Moderate Honey 4 Expensive Ice 0.5 Cheap The formulas that you can use are:
- VLOOKUP: =VLOOKUP(A2, D2:E4, 2, TRUE), where D2:E4 is a reference table that contains the price ranges and the categories, as shown below:
TablePrice Category 0 Cheap 1 Moderate 3 Expensive - INDEX and MATCH: =INDEX(F2:F4, MATCH(B2, E2:E4, 1)), where F2:F4 is the range that contains the categories, and E2:E4 is the range that contains the price ranges, as shown below:
TablePrice Category 0 Cheap 1 Moderate 3 Expensive - IF and IFS: =IFS(B2<1, “Cheap”, B2>=1, “Moderate”, B2>3, “Expensive”, TRUE, “Unknown”)
As you can see, each method gives the same result, but they have different syntax and features. You can choose the method that suits your needs and preferences, and use it to perform a lookup operation in Excel.
