Define specific cells as per the maximum value

Microsoft Excel
You want to display the content of one
result cell in another which is to the
left of the relevant maximum value of
a specifi c data fi eld in the table sheet.



This task can be carried out using
a combination of three simple functions.
The functions are used in one single
formula. Just imagine: The fi elds “B1” to
“B4” contain four numbers. If the largest
number is in “B3”, the result cell should
return the value from “A3”. For this, enter
the formula given here:
=INDEX(A1:A4,
MATCH(MAX(B1:B4),B1:B4,0),1)
“MAX(B1:B4) fi rst identifi es the largest
value from the data set. This value serves
as a search criterion for the comparison
that is extended to the search matrix
“B1:B4”. With the comparison type “0”,
the function identifi es the position of the
fi rst value from the search matrix that is
identical to the search criteria.
The matrix variant of the “INDEX”
function then uses the searched value
from the value matrix “A1:A4” that is
located in the fi rst column of the row
defi ned by the comparison.