From the following data:

Customer Product Value

A X 90

A Y 80

A Z 70

B X 60

B Y 50

B Z 40

C X 30

C Y 20

C Z 10

I am desperately trying to return the product with the 2nd highest value of each customer.

So basically my lookup value would be AND my customer (column A) AND the value (column C).

For example, in cell E2 i have the value "B", and Im trying to return the value "Y" in cell F2 with just one and the same Vertical lookup_array.

I tried

INDEX($B$2:$B$10;MATCH(1;(LARGE($C$2:$C$10;2)=$C$2:$C$10)*(E2=$A$2:$A$10);0);MATCH($B$1;$B$1;0))

but obviously, this didn't work since the second largest value of the whole lookup array is 80 (of customer A).

Is it even possible with these formulas or do I have to use VBA for this...?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!