Sometimes, you may need to look up a value in a column and return the corresponding value from another column in the same row. For example, you may have a list of products and their prices, and you want to find the price of a specific product. There are different ways to do this task in Excel, such as using the VLOOKUP, INDEX, or MATCH functions. In this article, I will explain how these functions work and how to use them in different scenarios.
VLOOKUP function
The VLOOKUP function is one of the most commonly used functions in Excel. It allows you to look up a value in the first column of a table and return the value from another column in the same row. The syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
where:
lookup_valueis the value that you want to find in the first column of the table.table_arrayis the range of cells that contains the table of data. The first column of the table should contain the lookup values.col_index_numis the number of the column in the table that contains the return values. The first column is 1, the second column is 2, and so on.range_lookupis an optional argument that specifies whether you want an exact match or an approximate match of the lookup value. If you enter TRUE or leave this argument blank, the function will return an approximate match, which means that the lookup value does not have to be exactly the same as the value in the first column, but it should be the closest value in ascending order. If you enter FALSE, the function will return an exact match, which means that the lookup value has to be exactly the same as the value in the first column.
Example of using VLOOKUP
Suppose you have the following table of products and their prices in cells A1:B6:
| Product | Price |
|---|---|
| Apple | 2.5 |
| Banana | 1.2 |
| Cherry | 3.6 |
| Date | 4.2 |
| Eggplant | 2.8 |
If you want to find the price of Cherry, you can use the following formula in any cell other than A1:B6:
=VLOOKUP("Cherry",A1:B6,2,FALSE)
This formula will return 3.6, which is the price of Cherry in column B. Note that we used FALSE as the last argument, because we want an exact match of the lookup value.
If you want to find the price of the product that is entered in cell D1, you can use the following formula in any cell other than A1:B6 and D1:
=VLOOKUP(D1,A1:B6,2,FALSE)
This formula will return the price of the product that is entered in cell D1, as long as it is exactly the same as one of the values in column A. For example, if you enter Banana in cell D1, the formula will return 1.2. If you enter Carrot in cell D1, the formula will return #N/A, which means that the lookup value is not found in the table.
INDEX function
The INDEX function is another way to look up a value in a column and return the corresponding value from another column in the same row. The INDEX function returns the value at a specific position in a range of cells. The syntax of the INDEX function is:
=INDEX(array, row_num, [column_num])
where:
arrayis the range of cells that contains the data.row_numis the number of the row in the array that contains the value that you want to return. The first row is 1, the second row is 2, and so on.column_numis an optional argument that specifies the number of the column in the array that contains the value that you want to return. The first column is 1, the second column is 2, and so on. If you omit this argument, the function will return the value in the first column of the array.
Example of using INDEX
Using the same table of products and prices as before, if you want to find the price of Cherry, you can use the following formula in any cell other than A1:B6:
=INDEX(B1:B6,3)
This formula will return 3.6, which is the price of Cherry in column B. Note that we used 3 as the second argument, because Cherry is in the third row of the array B1:B6. We omitted the third argument, because we want the value in the first column of the array, which is column B.
If you want to find the price of the product that is entered in cell D1, you can use the following formula in any cell other than A1:B6 and D1:
=INDEX(B1:B6,MATCH(D1,A1:A6,0))
This formula will return the price of the product that is entered in cell D1, as long as it is exactly the same as one of the values in column A. For example, if you enter Banana in cell D1, the formula will return 1.2. If you enter Carrot in cell D1, the formula will return #N/A, which means that the lookup value is not found in the table.
MATCH function
The MATCH function is a helper function that is often used with the INDEX function. The MATCH function returns the position of a value in a range of cells. The syntax of the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
where:
lookup_valueis the value that you want to find in the range of cells.lookup_arrayis the range of cells that contains the lookup values. The range can be either a single row or a single column.match_typeis an optional argument that specifies how you want to match the lookup value. If you enter 1 or leave this argument blank, the function will return the largest value that is less than or equal to the lookup value, assuming that the lookup array is sorted in ascending order. If you enter 0, the function will return the exact match of the lookup value, or #N/A if not found. If you enter -1, the function will return the smallest value that is greater than or equal to the lookup value, assuming that the lookup array is sorted in descending order.
Example of using MATCH
Using the same table of products and prices as before, if you want to find the position of Cherry in column A, you can use the following formula in any cell other than A1:B6:
=MATCH("Cherry",A1:A6,0)
This formula will return 3, which is the position of Cherry in column A. Note that we used 0 as the last argument, because we want an exact match of the lookup value.
If you want to find the position of the product that is entered in cell D1 in column A, you can use the following formula in any cell other than A1:B6 and D1:
=MATCH(D1,A1:A6,0)
This formula will return the position of the product that is entered in cell D1 in column A, as long as it is exactly the same as one of the values in column A. For example, if you enter Banana in cell D1, the formula will return 2. If you enter Carrot in cell D1, the formula will return #N/A, which means that the lookup value is not found in the table.
Comparison of VLOOKUP, INDEX, and MATCH functions
The VLOOKUP, INDEX, and MATCH functions can all be used to look up a value in a column and return the corresponding value from another column in the same row. However, there are some differences and advantages of each function.
- The VLOOKUP function is simpler and faster to use, but it has some limitations. It can only look up a value in the first column of a table and return the value from another column in the same row. It also requires the table to be sorted in ascending order if you want to use an approximate match. If you insert or delete columns in the table, you may need to adjust the column index number in the formula.
- The INDEX and MATCH functions are more flexible and powerful, but they require more steps and calculations. They can look up a value in any column of a table and return the value from another column in the same row. They also do not require the table to be sorted in any order. If you insert or delete columns in the table, the formula will automatically adjust to the new structure.
