Offset Match Max - Priority Issue
Results 1 to 3 of 3

Thread: Offset Match Max - Priority Issue
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Offset Match Max - Priority Issue

    Hi folks,

    I am relatively new to Excel functions, but have set up a shared sheet in Google docs for my friends and I to do an online auction for our Fantasy Football draft in real time.

    I have each of our names in a separate column across row 11, and a blank cell below each name in row 12 for us to enter a bidding amount. I have a cell set up with the following formula to display the current high bid:

    =MAX(A12:L12)

    Next to it, I want to display the name of the high bidder, so I am using this:

    =OFFSET(A12,-1,match(MAX(A12:L12),A12:L12,0)-1)

    It works almost perfectly, BUT when two people enter the same amount, it will automatically display the value of the column further to the left, regardless of who entered it first. I.e. if the person in Column E bids 55, they will appear in the high bid box. If the person in Column C also bids the same amount, their name will replace the original bidder in the high bid box. But if someone in column J then bids 55, it will not kick out the previous bidder unless person J bids higher.

    Is there any way around Excel reading the data from left to right and prioritizing the column furthest to the left? I feel like I'm so close to getting this to work right, I'd hate for there not to be a way to iron out this last kink. Thanks!

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,046
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Offset Match Max - Priority Issue

    This is an array formula. Confirm with Control+Shift+Enter.

    =INDEX(A11:L11,1,MAX(IF(MAX(A12:L12)=A12:L12,COLUMN(A12:L12),0)))
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Offset Match Max - Priority Issue

    Thank you for the suggestion. Just gave it a try and it actually reversed the problem, so equal values entered in columns to the right of the previous entry are now being prioritized. Entries to the left are no longer being prioritized.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •