Help me get away from nested IF/ANDs

[cdterford]

Excel 2010
Right now, I am working on a table that is going to count the amount of each part purchased. Then, I want to have a cell that will check which cell is highest, and report a cell that is adjacent to the highest one. It sounds complicated, but the picture should make it make sense. Currently, I can do this. BUT, I can only do it using messy, time-consuming nested IF/AND functions. I'm sure there is a simple way to do this with VLOOKUP, HLOOKUP, or INDEX/MATCH, but I really just have no idea how. Here's the ugly code I'm using; and there is a section that i haven't done yet with 9 (!) boxes, instead of this, which has 4 -- I would really like to not have to type all that out with nested IF/AND.

=IF(AND(H5>H6,H5>H7,H5>H8),G5,IF(AND(H6>H5,H6>H7,H6>H8),G6,IF(AND(H7>H5,H7>H6,H7>H8),G7,IF(AND(H8>H5,H8>H6,H8>H7),G8,""))))
This is the code I am using for J5 right now. As mentioned, this is all for only comparing 4 cells. The rows below have 9 to compare, and the code will get very long and messy.

This is the aforementioned picture. So, basically the "number purchased" column shows just that, by counting how many of each part is recorded in the blue area to the left. Simple. The "Most purchased" column is to look at the highest value from the "number purchased" column, and record the Model Number of that part (which is blacked out) that is in column G. In this instance, G3 and J3 say the same thing; G4 and L3 also say the same thing. Is there a simple way to do this without using the above nested IF code?

Thanks!

 

[SpillerBD]

=index(g5:g8,match(max(h5:h8),h5:h8))

 

[steve the fish]

This works for all instances except where there is equal values within H5:H8. Yours would produce "". This produces the first found match:

=INDEX($G$5:$G$8,MATCH(MAX($H$5:$H$8),$H$5:$H$8,0))

 

[vds1]

Hello,

What would be the result if two items are purchased at the same quantity ? Your formula would return blank.

 

[cdterford]

=index(g5:g8,match(max(h5:h8),h5:h8))

=index(g5:g8,match(max(h5:h8),h5:h8))

Thank you so much, both of you! This is exactly what I needed.

 

[cdterford]

Your formula would return blank.

Yes, the intention would be to simply leave this blank if they were equal. It isn't a big deal, as the purpose of this is to be used for ALL sales of these parts. So, after about a week, there likely won't be an instance where they are all equal. Though I appreciate your concern!

 

[SpillerBD]

Steve's is right
=INDEX($G$5:$G$8,MATCH(MAX($H$5:$H$8),$H$5:$H$8,0))

If it is not set for Exact Match, it could actually fail for sort order issues.... (It's amazing how many times I forget to use it when I almost always need it.)