Find a value combining two criteria (formulas)

Greetings,

I am having several problems with creating a formula.
I am performing a pairwise comparison (eg. Criteria 1 vs Criteria 2, 3, 4... / Criteria 2 vs Criteria 3, 4, 5... / Criteria 3 vs Criteria 4, 5, 6, etc).

Explanation:
- Columns A and F have the weights of each criterion (naturally, there are duplicates values)
- The column "H" has the division of scores C/D

I need to create these two formulas:

1) I need to select the value in column "H" that meet two criteria: Correspond to the row of the Criteria in column "B" with the highest weight in "A" PLUS Correspond to the row of the Criteria in column "E" with the SECOND highest weight in "F".
For example, if Criterion 4 has the highest weight in column A and Criterion 8 has the SECOND highest weight in Column F, I need a formula to automatically get that 1/2 in column "H"

2. I need to select the value in column "H" that meet two criteria: Correspond to the row of the Criteria in column "B" with the SECOND highest weight in "A" PLUS Correspond to the row of the Criteria in column "E" with the THIRD highest weight in "F".

This task is very urgent and I would really appreciate your help!

For 1st and 2nd highest

Formula:

Please Login or Register  to view this content.

For 2nd and 3rd highest

Formula:

Please Login or Register  to view this content.

I get 1/3 and 1/6 respectively (Rows 27 and 17)

Originally Posted by ChemistB

For 1st and 2nd highest

Formula:

Please Login or Register  to view this content.

For 2nd and 3rd highest

Formula:

Please Login or Register  to view this content.

I get 1/3 and 1/6 respectively (Rows 27 and 17)

Thank you very much for your answer!

I have two doubts:
For 1st and 2nd highest I also get 1/3. However, I think it should be 1/2 (row 30).
Explanation: The 1st highest value for Criteria in Column B is 0.128865671. The 2nd highest value for Criteria in column E (for THIS 0.128865671 in column A) is 0.129372485. The value of 0.357178132 is the 1st highest. Hence, I get C30 / D30 --> 1/2.
Could you help me, please?

For 2nd and 3rd highest, I didn't get 1/6. I got 1/3. But I think this 1/3 is correct.

Thanks in advance!
This is very important for me, because I need to get 10 values:
1st highest (reference Column A) and 2nd highest (reference Column F)
1st highest (reference Column A) and 3rd highest (reference Column F)
1st highest (reference Column A) and 4th highest (reference Column F)
1st highest (reference Column A) and 5th highest (reference Column F)
2nd highest (reference Column A) and 3rd highest (reference Column F)
2nd highest (reference Column A) and 4th highest (reference Column F)
2nd highest (reference Column A) and 5th highest (reference Column F)
3rd highest (reference Column A) and 4th highest (reference Column F)
3rd highest (reference Column A) and 5th highest (reference Column F)
4th highest (reference Column A) and 5th highest (reference Column F)

I believe that if I have the formulas for the 1 and 2, I could perform the remaining ones...

Okay, so you want the 2nd highest value in F in the group that has the first highest value in A, not just the 2nd highest value. Is that correct?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As youy are new, I'll do it for you this time: https://www.mrexcel.com/board/thread...mulas.1168026/.)

Did you understand the rule? Everyone here needs help, and everyone has to abide by our rules. No exceptions and no excuses. Thanks.

Yes, I've already put the cross-link in the other forum.

This formula requires you sort your column A weights lowest to highest.

Okay, I made a small table in J:L where J contains your required ranking for Col A, K has your ranking for Column F and L has your result.
In L3 copied down

Formula:

Please Login or Register  to view this content.

I spot checked it and appears to be working.

First, I think of getting a unique distinct list of "Criteria" first. The list could be extracted into somewhere in worksheet, or using offset.
I tried using offset.
I found out that with column B starts with "Criterio 1" (row #39), the corresponding "weight" in column F wil be running from criterio 2 to criterio 10, then F39:F47 should be unique list of weight. Correct me if the actual data said "no".
this is unique list:
OFFSET($F$2,MATCH("Criterio 1:*",$B$3:$B$100,0),,10,)

Then, with J1 contains column A ranking, K1: column F ranking (actuallly, K1+1, include A ranking)

Please Login or Register  to view this content.