vlookup with max

[abe914]

i'm trying to find the salespersons largest month using vlookup and max. can't seem to get the syntax right. any help is greatly appreciated.

vlookup-max.jpg

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Jonmo1]

Why can't you just put this in B8 and drag down

=MAX(B2:M2)

[Jonmo1]

Are you trying to get the name of the month that has the largest value for each person?

Try

=INDEX(B$1:M$1,MATCH(MAX(B2:M2),B2:M2,0))

[oeldere]

since you have excel 2016 use power pivot to re-arange your data.

After that a pivot table to analyse the data.

[abe914]

in my real world example i have 2 seperate tables on 2 different worksheets.
one table has all the monthly sales for each salesperson and the other table only has the salespersons name and would like to retreive the largest month for that saleperson.

[Jonmo1]

Do you want to return the largest value for the person, or the name of the month (Jan Feb etc) where that largest value occurred ??

Can you attach a sample book?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[abe914]

i would like to to return the largest value for the person

[oeldere]

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Jonmo1]

Try

=MAX(INDEX(B$2:M$5,MATCH(A8,A$2:A$5,0),0))

[abe914]

that worked! thank you very much Jonmo1

[Jonmo1]

You're welcome.