I need help developing a formula in Microsoft Excel?

Question

I have two columns with data, column A and column B. Column A has titles of movies, and column B has prices of the corresponding movies. I have a cell which asks for the "title of highest price movie," I am trying to come up with a formula that does not return the max price, but returns the actual name of the movie in text that has the max price. Can anyone help me out? It's greatly appreciated.

It should be very basic. I'm not sure it has anything to do with Lookup.

I can get the max price in number form easily just by doing the Max function. But, I need more than just the Max function. I need text corresponding to that max result.

Please don't tell me to sort.

I;'ve figured it out using Index, Match, and Max, functions. Thank you for taking the time to answer my question.

Answer 1

=INDEX(movies,MATCH(MAX(Prices),Prices))

oops, I just saw your added note.

Answer 2

Now, you seem to already know enough about Excel to know that it works on algorithms to do totaling of figures in rows for you and can also return specific field answers when programmed to do so.

From what you've said, you've already configured how to have it return the max price and the item, also, isn't it? Because it can't just return the max Price without the rest of that line, can it?

If it's returning just the max price with no link as to which line it's in or the whole line itself, then there's a glitch in your programming.

Perhaps the best forum to help you with this is the one for which I've given you a link below, of Excel Experts and Excel Help.

The other links that I've provided you with will take you to the Microsoft Training and other Training sites where you might find the answers directly.

Hope this helps resolve the issue.

Cheers.

Answer 3

It would seem easier just to SORT the entire sheet by price and the most expensive one would be at the top of the column, next to it's title. No formula needed.

Answer 4

=MAX(B1:B10)
This is a formula given on site, that produces the Max price.
You should get a Void, or 0 in all other cells in Col. B. Am I right?

Can you use a third Col "C"?
Formula in Col. C............If(B2>"",A2,"")
Deciphered> If the price in B2 is more than a void (" "), then print the title in A2, otherwise "print" a void, (or 0, your choice)

I used to be able to do this simplified, in Lotus 123. Long passe. Forgot without a book. Never did this in Excel.

Answer 5

I have played aroung with this and have 2 solutions:

1) Nested IF statements--cumbersome and very long.

2)In cell B9: =VLOOKUP(A9,A1:B7,2,FALSE), where the table range is A1:B7; cell A9: =MAX(A1:A7). But in this case, I had to reverse your columns putting prices in column A and locations in column B. I have not figured out why this formula (altered) will not work when the columns are reversed. If you figure this out, please let me know. Thanx

Answer 6

use formular to calculate MAX

=MAX(B1:B10)

assumming that B1:B10 is the columns with price cells.

You can use MAXA also.

Just go to Help option IN MS Excel, and type in your questions, you will find the answers. Good luck.

Answer 7

I would need to be able to communicate with you which is impossible in this thread where I get only one answer.
Here is a site you can go to for quick, free, professional help from other users.
http://forums.techguy.org/
Register and then go to the "Business Applications" Forum and ask your question there. You'll be happy with the result.

Answer 8

press F1 and browse.. through it.. you will find the solution..lol