How to find max of numbers returned by using vlookup with each of a column of names

RuthB

New Member
Joined
Jun 28, 2010
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a worksheet with several columns of names (in columns C to U). Some names occur only once, while others occur in more than one column. Within each column, however, each name occurs only once, and they are in alphabetical order.

Meanwhile, column W has a complete list of all the names, once each, in order. Column X has some other data, and then column Y has some numbers I need to retrieve.

What I want to find is, for each of my columns C to U, what is the max of all the numbers corresponding to the names in that column.

I tried with {=MAX(VLOOKUP(C94:C107,$W$94:$Y$224,3))} but it just looks up C94 in $W$94:$Y$224 and returns that value, rather than the max of all the numbers corresponding to names in the C column.

What am I doing wrong, please?

Thanks

PS Ideally, I'd like it to be able to ignore blank cells in the columns of names, because my lists of names in the columns are all different lengths.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
How about
=MAX(iferror(VLOOKUP(C94:C107,$W$94:$Y$224,3,0),0))
 

RuthB

New Member
Joined
Jun 28, 2010
Messages
11
Office Version
  1. 365
Platform
  1. Windows
It works. Thank you so much, Fluff. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top