Find max value/max duplicate and then vlookup.

[alan2231]

I have an excel document which consists of two array.

Left Array consists of 4 rows which have values from 5-1 (5 beings the highest and 1 being the lowest). The values A,B,C,D,E,F are simplify reference points. W

Right Array consists of the cells that corresponds to the cell from the Left array. For example the left array (A,5) corresponds to A5 in the right array. (D,3) corresponds to D3 and so forth.

A

B

C

D

E

F

A

B

C

D

E

F

5

5

5

4

4

4

5​

A5

B5

C5

D5

E5

F5

5

5

1

1

1

1

4​

A4

B4

C4

D4

E4

F4

4

3

3

3

3

2

3​

A3

B3

C3

D3

D3

F3

2

1

1

1

1

1

2​

A2

B2

C2

D2

D2

F2

1​

A1

B1

C1

D1

D1

F1

What I would to know is how to I read each rows from the left array and find the maximum possible value (if duplicates exists take all of them) and then vlook up their corresponding cell in the right array.

So for example
ROW (5,5,5,4,4,4) the max values are 5,5,5 which correspond to A,B and C and the expected result is A5,B5,C5.
ROW (5,5,1,1,1,1) the max values are 5,5, which correspond to A and B and the expected result is A5,B5.
ROW (4,3,3,3,3,2) the max value is 4 which correspond to A and the expected result is 4A

Thank you and I hope I was clear.

 

[alan2231]

I have attached an image for the code above.