Lookup to return multiple cell values

[gee83]

Hi I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value.
So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved. Thanks.

[Pepe Le Mokko]

Something like this dragged to the right as needed?

Please Login or Register  to view this content.

[alvin-chung]

Perhaps this is what you're looking for?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[gee83]

Thanks, this looks like what I am after, however I have tried using your formulas in my actual workbook and amended all of the cell numbers but the cells which should show the results are just blank, no error message. Do you know what could be wrong?

[alvin-chung]

You're welcome
It would be hard to imagine what went wrong unless you post the workbook here..

ps: remember make a copy and remove any confidential in the workbook before you upload



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[gee83]

Thanks, I've (modified and) attached my file (sample report 0302).

[gee83]

Sorry to bump, but can anyone take a look at this and my attached example report? Many thanks, Gareth.

[gee83]

Can anyone take a look at my sample report attached? I have entered formulas but getting blank cells returned, not even any error. It could be something simple??

[alvin-chung]

Hi gee83,

Couple of things to share for your working report
1. the arrangement of the fields were quite different with the sample report, thus formula needs to be tweaked
2. you're entering normal formula instead of array formula so it won't be working as expected (you're to press ctrl+shift+enter instead of just enter to produce array formula)
3. you're using range with blank cells (row 41-8992) thus need different way of handling it

I went through you working report and revise the solution as follow:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin