Home

Offset Match Max - Priority Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-799357%22%20slang%3D%22en-US%22%3EOffset%20Match%20Max%20-%20Priority%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799357%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EHi%20folks%2C%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20am%20relatively%20new%20to%20Excel%20functions%2C%20but%20have%20set%20up%20a%20shared%20sheet%20in%20Google%20docs%20for%20my%20friends%20and%20I%20to%20do%20an%20online%20auction%20for%20our%20Fantasy%20Football%20draft%20in%20real%20time.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20have%20each%20of%20our%20names%20in%20a%20separate%20column%20across%20row%2011%2C%20and%20a%20blank%20cell%20below%20each%20name%20in%20row%2012%20for%20us%20to%20enter%20a%20bidding%20amount.%20I%20have%20a%20cell%20set%20up%20with%20the%20following%20formula%20to%20display%20the%20current%20high%20bid%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3DMAX(A12%3AL12)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ENext%20to%20it%2C%20I%20want%20to%20display%20the%20name%20of%20the%20high%20bidder%2C%20so%20I%20am%20using%20this%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3DOFFSET(A12%2C-1%2Cmatch(MAX(A12%3AL12)%2CA12%3AL12%2C0)-1)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EIt%20works%20almost%20perfectly%2C%20BUT%20when%20two%20people%20enter%20the%20same%20amount%2C%20it%20will%20automatically%20display%20the%20value%20of%20the%20column%20further%20to%20the%20left%2C%20regardless%20of%20who%20entered%20it%20first.%20I.e.%20if%20the%20person%20in%20Column%20E%20bids%2055%2C%20they%20will%20appear%20in%20the%20high%20bid%20box.%20If%20the%20person%20in%20Column%20C%20also%20bids%20the%20same%20amount%2C%20their%20name%20will%20replace%20the%20original%20bidder%20in%20the%20high%20bid%20box.%20But%20if%20someone%20in%20column%20J%20then%20bids%2055%2C%20it%20will%20not%20kick%20out%20the%20previous%20bidder%20unless%20person%20J%20bids%20higher.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EIs%20there%20any%20way%20around%20Excel%20reading%20the%20data%20from%20left%20to%20right%20and%20prioritizing%20the%20column%20furthest%20to%20the%20left%3F%20I%20feel%20like%20I'm%20so%20close%20to%20getting%20this%20to%20work%20right%2C%20I'd%20hate%20for%20there%20not%20to%20be%20a%20way%20to%20iron%20out%20this%20last%20kink.%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-799357%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799429%22%20slang%3D%22en-US%22%3ERe%3A%20Offset%20Match%20Max%20-%20Priority%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799429%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390613%22%20target%3D%22_blank%22%3E%40dejalive%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20can%20try%20this%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOFFSET(%20A12%2C-1%2CMATCH(2%2CIF(A12%3AL12%3DMAX(%24A%2412%3A%24L%2412)%2C1%2CFALSE))-1)%3C%2FP%3E%3CP%3Ehope%20it%20helps!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-799540%22%20slang%3D%22en-US%22%3ERe%3A%20Offset%20Match%20Max%20-%20Priority%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390613%22%20target%3D%22_blank%22%3E%40dejalive%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DLOOKUP(2%2C1%2F(%24A%2412%3A%24L%2412%3DMAX(%24A%2412%3A%24L%2412))%2C%24A%2411%3A%24L%2411)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
dejalive
Occasional Visitor

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 Replies

@dejalive 

you can try this 

=OFFSET( A12,-1,MATCH(2,IF(A12:L12=MAX($A$12:$L$12),1,FALSE))-1)

hope it helps!! 

@dejalive 

That could be

=LOOKUP(2,1/($A$12:$L$12=MAX($A$12:$L$12)),$A$11:$L$11)