I have text information in columns A through H. I have numerical data in columns I onward. My goal is to find the maximum numerical value in all columns I and onward, and return the corresponding name from column H.
For example, the maximum number in column J is 0.382, located at J88. So I want Excel to return the text content of H88.
I tried doing VLOOKUP but it seems like this was only working if my column of names/text is adjacent to my column of numerical values, so that was a dead end.
What you need is combination of INDEX() and MATCH():
=INDEX(H:H;MATCH(MAX(J:J);J:J;0))
This formula finds position of the highest value in the J:J column than returns cell on the same position in the H:H column.
VLOOKUP() doesn't work in this case because you need to mind order of the columns with the function, if you swapped them it would work just fine.
Just to elaborate on @M.Douda's answer, since it may not be clear that this approach can cause unintended mistakes if you have more than one maximum.
index() and match() is definitely the way to go, but you need to be careful if there is more than one maximum (i.e. if both J88 and J89 have a value of 0.382). With the match_type parameter equal to 0, values can be in any order and Excel will return the first match of the search, but only that one.
Note: If you wanted to keep using vlookup(), a simple solution would be to add one column in the end (to the right) where you copy from column H (but this is obviously redundant, not recommended). It also suffers from the maxima problem.
