Indexing larger value

[esupply]

I need a index formula to find a larger value out of 2 rows please see attached sheet ,,its easier to see than it is to explain it thanks for your help everyone

[NBVC]

If we can assume that the names in column Y would be grouped together, then:

=IFERROR(MAX(INDEX(Ground3!$A$2:$AQ$10,MATCH($B$2,Ground3!$Y$2:$Y$10,0),MATCH($S$1,Ground3!$A$1:$AQ$1,0)):INDEX(Ground3!$A$2:$AQ$10,MATCH(2,1/(Ground3!$Y$2:$Y$10=B2)),MATCH($S$1,Ground3!$A$1:$AQ$1,0))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

[esupply]

Well it works but it dosent not on all of them even with the confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

[NBVC]

Are the names grouped together on the Ground3!Sheet? That is what I had disclaimered.

[esupply]

Yes they are all in column y

[NBVC]

I mean are all the Jason Devlins together within column Y?

If so, formula above should work...

If not, try:

=IFERROR(MAX(IF(INDEX(Ground3!$Y$2:$Y$10,MATCH($B$2,Ground3!$Y$2:$Y$10,0)):INDEX(Ground3!$Y$2:$Y$10,MATCH(2,1/(Ground3!$Y$2:$Y$10=B3)))=B2,INDEX(Ground3!$A$2:$AQ$10,MATCH($B$2,Ground3!$Y$2:$Y$10,0),MATCH($S$1,Ground3!$A$1:$AQ$1,0)):INDEX(Ground3!$A$2:$AQ$10,MATCH(2,1/(Ground3!$Y$2:$Y$10=B2)),MATCH($S$1,Ground3!$A$1:$AQ$1,0)))),"")

confirmed with CSE

[esupply]

Ok i added some more info for you to look at to see what im looking for,,,look at sheet 3 in column Y you will see duplicate names,well that means they had 2 shipments ,,,what i need to find or get in column S is the higher dollar value in column T

[NBVC]

Do you know for sure it is always column T? Not sure why you where using this part: MATCH($S$1,Ground3!$A$1:$AQ$1,0)... is it a moving target?

[NBVC]

=IFERROR(MAX(IF(Ground3!$Y$2:$Y$10=B3,INDEX(Ground3!$A$2:$AQ$10,,MATCH($S$1,Ground3!$A$1:$AQ$1,0)))),"")

or, if you know it is always column T

=IFERROR(MAX(IF(Ground3!$Y$2:$Y$10=B2,Ground3!$T$2:$T$10)),"")

[esupply]

Ok well those 2 bring up a 0 value,, i got one of the other ones to work. you have to glance it over because every now and then it will pull in a number thats not even on the sheet,,,I dont know where its coming from either,,,But that you for your time NBVC

[NBVC]

In your last sample workbook... the names in column B of Sheet1 do not appear in Sheet3, column Y.. that is why you get 0.

Formula adapted to Sheet3, is

=IFERROR(MAX(IF(Sheet3!$Y$2:$Y$43=B2,Sheet3!$T$2:$T$43)),"")

confirmed with CSE... put a name in B2 that is actually part of Sheet3!$T$2:$T$43 and test...

[esupply]

Ok i think we both may be confused or just me,,i have added a new sheet with more examples the way they should be, that was my fault for mixing them up the way i did. Sorry

[NBVC]

The same formula I have been showing you gave me the results you are asking for...

[esupply]

thanks alot,,like i said my brain is in the gutter,,,it wont let me give you more points today,,,thanks for all your help