I want to check the Money Collected column and figure out which cell has the highest value. I then want to display the highest value, with the corresponding name in the cell next to the “Current Frisbee Selling Leader” cell. Is this possible, and if so, can you guys help me out?
I.e. Corey has the highest money total, so his name and number should be displayed next to the Current Frisbee Selling Leader cell.
So, I figured out how to find the highest values cell, but now the question is, how do I attach the name to the money earned cell? Corey’s $10 is showing as the highest selling person, but I want it to say: Corey - $10.
EDIT: I have edited the document, so you can see that I want the name to be first in one cell, then the amount he earned in the next. Let me know what I have to do to get it to work!
Thought you will just need to highlight all the columns that you want to sort and then go into the Data tab.
After that, it will be pretty self-explanatory.
But I think it will help if you have proper header for your columns, e.g. Name, Amount, etc.
I’m not 100% sure what you mean, but in the Data Tab it only allows me to sort alphabetically…
If Corey is the current leader with $10, the amount will show up in the correct cell, but I don’t know how to get his name there next to the amount he has earned.
I want this table to auto-update so I only type in numbers, and if there is a new leader, excel knows what to do.
Well, I guess both, but I am building it in Google Spreadsheets because I can share it with the team and they can update it when they get new donations. I think both are relatively similar. If I know how to do it in Excel, I can probably figure out how to do it in Google Spreadsheets.
What you want to do can be done in Excel, but I don’t understand Google Spreadsheets very well. This is an easy way to do it that will work provided you don’t have too many team members to compare.
The question (and answer) in this link sound pretty similar to what you want to do. Also, a simple MAX function will find the highest value, if you’re not concerned about the formatting and color.
Yes, I got the MAX value done under the Money Collected cell. It shows the highest value from the set cells.
=MAX(E6:E10, E18:E24)
I now want to somehow include the name with the value. So if Corey has $10, I want his name to show up with his number, but on a different cell (or the same, I don’t care). But if I raise, $15, my name and amount raised will show up in the cell(s)
Some formula like:
If MAX = cell E(n), write; Name
Something like that.
You give it a lookup value, a cell range, and a a number (call this n). It returns the value in the same row as the lookup value in the nth column in the range.
You would be using HLOOKUP not VLOOKUP (Horizontal table not vertical). However, the first column is supposed to be the looked up value. In your case it’s the other way around. Long story short, neither lookup function will work. I got this to work
The indirect function finds the value at the cell address. The if statement finds the largest value between the two tables and returns the row. For whatever reason, when I tried combining the two sets of rows, it didn’t work. I’m sure you could get it to work if you really wanted.
By the way, the function will return the FIRST person with the largest value. IOW, If there’s a tie, the first person with the largest value is what’s displayed.
What you do in this case, and what I did in the .xlsx file I linked, is put a hidden column on the left (top in the case of HLOOKUP) that just copies the column containing the lookup value. In this case, the hidden column just copies the prices. Then you can use lookup functions.
Really, lookup functions should be able to see things to the left of the lookup column as well as the right, but we work with what we have.
