Looking for: return max value and neighboring cell

[msaz87]

Hi all,

I'm trying to set up a record sheet of sorts that would have various record categories automatically picking out the proper values from another sheet. I've found that the MAX function works great for this, but I need information from neighboring cells as well.

For instance... on the sheet of data that the records are picking from, there will be three columns, one for team, one for name and one for number of points. I can use MAX to search through all the people listed and get the highest number of points, but I'd like to have the data accompanied with the person's name and team if possible.

Hopefully that makes sense... any help is appreciated. I'm running Excel 07... Thanks!

[Portuga]

Have you considered doing a pivot table from your data?

[msaz87]

Pivot Tables are always an option, but it's not quite as flexible in format as I'd like them. So I was just hoping there might be a way to do it a different way. Possibly use the MAX function to locate the stat, then a function that locates the other cell based on the one MAX finds? I'm just thinking out loud and really don't have an idea -- so any ideas would be helpful.

Thanks a bunch

[msaz87]

Any ideas?

Thanks

[Portuga]

You can use a Vlookup formula, but it will only work if its searching in columns to the right of the "Max" value. Vlookups wont work from right to left (at least in 2003, not sure on 2007)

What you can do is to insert a new column (As columnA) called "Points duplicate" where you would have =D1 and drag dow (I´m considering D1 and your "Points" column).

You could then use the Vlookup to look for the max value (you already have) and return its adjacent cell values.

[Portuga]

I just learned about this formula combination today. Have a look.

(attached)