CODEWITHSUNDEEP
excelexcel-formulavlookup
SeanG123
SeanG123

Reputation: 21

Excel: How to find Max value of row using a Vlookup

I have a table like this:

Name:    Jan18: Feb18: Mar18:.......Aug20:
Tom       1.5%   4.3%  6.7%  ....... 5.4%
Pat       29.7%  0.1%  0.0%  ....... 11.0%
Bill      54.2%  91.0% 9.7%  ....... 12.3%
...      .................................
Mary      9.0%   10.0% 0.5% ........ 0.4%

I am trying to create a column in a separate worksheet called "Max 1month %" - As you can imagine this finds the max month across each row and fills the cell in the new column. The only thing is, I need a Vlookup to do this as it won't always be the same data.

Example new worksheet:

Name:   Max 1month%: 
Tom       6.7%       
Pat       29.7%         
Bill      91.0%        
...      .......
Mary      10.0%

I can't find any tutorials on this online. Is this possible in excel? Any help is appreciated, thank you.

Upvotes: 1

Views: 774

Answers (2)

JvdV
JvdV

Reputation: 75850

Try:

=MAX(INDEX(B:M,MATCH("Tom",A:A,0),))

For example as below:

enter image description here

Formula in B8:

=MAX(INDEX(B$1:E$5,MATCH(A8;A$1:A$5,0),))

Upvotes: 1

basic
basic

Reputation: 11968

Use AGGREGATE function:

=AGGREGATE(14,4,(A10=$A$2:$A$5)*$B$2:$E$5,1)

enter image description here

Upvotes: 3

Related Questions