Lookup cell value in a column based on max value in another column?

[megaman777]

Hello again,

I have a worksheet with a number of rows, each is unique representing a project. Where column A is the project name and column N is the number of days open, how would I populate a cell on another worksheet to show me the name of the project with the highest number of days open? Is this possible to do for top 5 highest open?

 

[megaman777]

I should add, I would need to qualify the lookup with a criteria based on another column, say column H which contains project type.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[megaman777]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I did try, sadly our work computers are so violently locked down it won't even let me access the site.

 

[Fluff]

Maybe

=TAKE(SORT(FILTER(A2:N1000,H2:H1000="X"),14,-1),1,1)

 

[megaman777]

Maybe

=TAKE(SORT(FILTER(A2:N1000,H2:H1000="X"),14,-1),1,1)

Really appreciate your help. I gave this a go but couldn't get it to work.

I kinda solved my issue by using an if/large to isolate the top 5 values, then making a table using match/offsets. It's a bit clumsy but it does the job. Cheers!

 

[Fluff]

Glad you sorted it & thanks for letting us know.