Help: Find a value combining criteria (formulas)

[ctorohuamanchumo]

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"
It is important to mention that, I want the 2nd highest value in F in the group that has the first highest value in A, not just the 2nd highest value

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".
It is important to mention that, I want the 3rd highest value in F in the group that has the second highest value in A.

This task is very urgent and I would really appreciate your help!
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)

 

[jtakw]

Hi,

I'm not sure, but if your Criterion are in Columns B and E, you might want to Translate those in English for us who don't know what it says ( I, for one, don't even know what Language it is ).

And also, providing a sample with XL2BB will be a good idea ( check my signature for where and how ).

 

[ctorohuamanchumo]

Hi,

I'm not sure, but if your Criterion are in Columns B and E, you might want to Translate those in English for us who don't know what it says ( I, for one, don't even know what Language it is ).

And also, providing a sample with XL2BB will be a good idea ( check my signature for where and how ).

Dear jtakw,

Thank you very much. I am having some troubles with XL2BB (I am at work and there are some internet restrictions).
However, I am attaching an updated image.

I need a formula that could find the value in column "H" (goes from H3 to H83) that meet these criteria:
Correspond to the row of the Criteria in column "B" with the highest weight overall in "A" PLUS Correspond to the row of the Criteria in column "E" with the SECOND highest weight in "F". It is important to mention that I want the 2nd highest value in F in the group that has the first highest value in A, not just the 2nd highest value overall.
For example, if Criterion 1 has the highest value, I would search for the second-highest value among those rows for Criteria 1. In these case, it would be row 9 (Criterion 8). Hence, the formula should give me the value 1/3.

Similarly, I need a formula that could find the value in column "H" that meet these criteria:
Correspond to the row of the Criteria in column "B" with the SECOND highest weight overall in "A" PLUS Correspond to the row of the Criteria in column "E" with the THIRD highest weight in "F".
It is important to mention that, I want the 3rd highest value in F in the group that has the second highest value in A, not just the 3rd highest value overall.
For example, if Criterion 2 has the second-highest value overall, I would search for the third-highest value among those rows for Criteria 2. In these case, it would be row 14 (Criterion 4). Hence, the formula should give me the value 1/7.

I believe that if I have the formulas for these, I could perform the remaining ones...

Thanks in advance,

Carlos J. Toro-Huamanchumo, MD

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find a value combining two criteria (formulas)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ctorohuamanchumo]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find a value combining two criteria (formulas)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks for the advice.
Similar post in this link: Find a value combining two criteria (formulas)