Lookup of largest value(s), then if multiple results, Lookup lowest of a second set of values

[bdsllc]

Hi everyone-

I’m looking to create a formula that will allow me to lookup the largest result(s) in one data range, and then if there are multiple results, a second lookup if performed for only those largest number in a second data range, looking for the lowest number in the second data range.

For example, here's an snapshot of a file I created: https://imgur.com/a/qRs65J1

I want to perform an hlookup of the highest value in row 18, and if there is more than on top value, then look through row 19 for only the columns that have the highest values, find the lesser of the amounts, and finally return the name of a service in row 20

To walk through this operation, the formula would look through row 18 and identify H18 & O18 as the two highest values, then it would look through row 19 for the lower value in either column H or O, identifying O19, and then return the value in O20 (Company E, Service 4).

I would prefer to use a formula rather that VBA or an array. Is this possible?

 

[DanteAmor]

I tried, but I could not find a regular formula. Try this array formula


{=INDEX(A20:S20,0,MATCH(MAX(D18:S18)&MIN(IF(D18:S18=MAX(D18:S18),D19:S19)),D18:S18&D19:S19,0)+3)}

 

[bdsllc]

Thanks alot, Dante. It works!

Any idea what I would have to change in the formula to get the second-highest number of features with the lowest price?

For example, in the the second highest number of features is 7, which appear in D18, G18, & N18. The formula would then look in row 19th and see that D19, and return "Company A, Service 1".

Again, thanks for your help. I was having a hard time figuring out the formula.

 

[DanteAmor]

Thanks alot, Dante. It works!

Any idea what I would have to change in the formula to get the second-highest number of features with the lowest price?

For example, in the the second highest number of features is 7, which appear in D18, G18, & N18. The formula would then look in row 19th and see that D19, and return "Company A, Service 1".

Again, thanks for your help. I was having a hard time figuring out the formula.

Try this array formula:

{=INDEX(A20:S20,0,MATCH(LARGE(D18:S18,COUNTIF(D18:S18,MAX(D18:S18))+1)&MIN(IF(D18:S18=LARGE(D18:S18,COUNTIF(D18:S18,MAX(D18:S18))+1),D19:S19)),D18:S18&D19:S19,0)+3)}

 

[bdsllc]

So, after some testing of your formula, I was getting incorrect results. This formula appears to work better:

{=INDEX(D20:S20, MATCH(MIN(IF(D18:S18=MAX(D18:S18),D19:S19, FALSE)), IF(D18:S18=MAX(D18:S18),D19:S19, FALSE), 0))}

 

[DanteAmor]

Perfect, the important thing is that it works for you. Thanks for the feedback.