Sometimes, you may want to update a cell based on the value of another cell that matches a column header from a list. For example, you may have a table of products and prices, and you want to update the price of a specific product that you enter in another cell.
One way to do this is to use a combination of INDEX and MATCH functions. The INDEX function returns a value from a range of cells based on a row and column number. The MATCH function returns the position of a value in a range of cells. By using these two functions together, you can look up the value of a cell that matches a column header from a list, and then return the value from another cell in the same row.
Procedures
To update a cell when column header matches a list in Excel formula, follow these steps:
- Enter the list of column headers in a range of cells, such as A1:A5.
- Enter the table of data in another range of cells, such as B1:F10. Make sure that the column headers in the table match the values in the list.
- Enter the value that you want to look up in another cell, such as G1.
- In the cell that you want to update, enter the formula:
=INDEX(B2:F10,MATCH(G1,A2:A10,0),MATCH(H1,B1:F1,0)) - Press Enter to get the result.
Explanation
The formula works as follows:
- The MATCH function in the second argument of the INDEX function looks for the value in G1 in the range A2:A10, and returns the row number where it is found. For example, if G1 is “Apple”, the MATCH function returns 2, because “Apple” is in the second row of A2:A10.
- The MATCH function in the third argument of the INDEX function looks for the value in H1 in the range B1:F1, and returns the column number where it is found. For example, if H1 is “Price”, the MATCH function returns 3, because “Price” is in the third column of B1:F1.
- The INDEX function then returns the value from the range B2:F10, based on the row and column numbers returned by the MATCH functions. For example, if G1 is “Apple” and H1 is “Price”, the INDEX function returns the value from B3, which is 2.5.
Example
Here is an example of how to use the formula to update a cell when column header matches a list in Excel formula.
| Product | Price | Quantity | Total |
|---|---|---|---|
| Apple | 2.5 | 10 | 25 |
| Banana | 1.5 | 15 | 22.5 |
| Cherry | 3 | 8 | 24 |
| Date | 4 | 6 | 24 |
| Elderberry | 5 | 4 | 20 |
The list of column headers is in A1:A5, and the table of data is in B1:F5. The value that we want to look up is in G1, and the column header that we want to match is in H1. The cell that we want to update is I1.
In I1, we enter the formula: =INDEX(B2:F5,MATCH(G1,A2:A5,0),MATCH(H1,B1:F1,0))
The formula returns the value from the table that matches the product and the column header. For example, if G1 is “Cherry” and H1 is “Quantity”, the formula returns 8, which is the quantity of cherry in the table.
Other approaches
There are other ways to update a cell when column header matches a list in Excel formula, such as using VLOOKUP or SUMIFS functions. However, the INDEX and MATCH combination is more flexible and versatile, as it can work in any direction and return exact matches by default. It can also handle multiple criteria and dynamic ranges. Therefore, it is recommended to use this approach for this task.
