MAX is the largest value in your dataset. Sometimes, you may need to check which is the largest value or mark in your dataset. This may involve the cell's address with the maximum value or the value itself. Several in-built functions can be used to check the cell with the maximum value. Let us now discuss some ways of locating the cell containing the MAX.
Using the ADDRESS Function
ADDRESS Function is one of the inbuilt functions found in Excel. The function returns the cell with the maximum value if true. Here are the steps to use this method:
1. Open the Excel application.
2. Open the Sheet that contains the dataset. You need to check the cell with the maximum number.
3. Locate an empty cell that will contain the formula and double-click on it. On the Formula Bar, type the Equal sign followed by the ADDRESS function. That is, = ADDRESS (
4. Then, type the MATCH function. That is, =ADDRESS (MATCH(
5. Next, add the MAX function that will check the cell with the maximum value. That is =ADDRESS (MATCH(MAX(
6. Add the cells that you need to check. That is, =ADDRESS (MATCH(MAX(B:B),B:B,0),1)
7. Finally, hit the Enter button.
Using the INDEX Function
The ADDRESS function returns the column and row index ($A$6), while the INDEX function only returns the row index. Below are steps to use this function to check the cell that contains the MAX.
1. Open the Excel application.
2. Open the Sheet that contains the dataset. You need to check the cell with the maximum number.
3. Locate an empty cell that will contain the formula and double-click on it. On the Formula Bar, type the Equal sign followed by the INDEX function. That is, = INDEX (
4. Then, type the MATCH function. That is, =INDEX (MATCH(
5. Next, add the MAX function that will check the cell with the maximum value. That is =INDEX (MATCH(MAX(
6. Add the cells that you need to check. That is, =INDEX (MATCH(MAX(B:B),B:B,0),1)
8. Finally, hit the Enter button.
Using the VLOOKUP Function
Here are the steps to follow:
1. Open the Excel application.
2. Open the Sheet that contains the dataset. You need to check the cell with the maximum number.
3. Locate an empty cell that will contain the formula and double-click on it. On the Formula Bar, type the Equal sign followed by the VLOOKUP function. That is, = VLOOKUP (
4. Then, type the MAX function and add the cell index, which contains the values you are checking. That is, =VLOOKUP(MAX($B$2:$B$8),
5. Next, add the range of columns with the dataset, column argument, and return (if false) argument. =VLOOKUP(MAX($B$2:$B$8), $B$2:$B$8, 1, FALSE)
Finally, hit the Enter button.
To check the maximum number in a dataset
Steps
1. Open the Excel application.
2. Open the Sheet that contains the dataset. You need to check the cell with the maximum number.
3. Locate an empty cell that will contain the formula and double-click on it. On the Formula Bar, type the Equal sign followed by the MAX function. That is, = MAX (
4. Select the range of your dataset and hit the Enter button.