How can I combine two formulas to find a value in a column in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-2270645%22%20slang%3D%22en-US%22%3EHow%20can%20I%20combine%20two%20formulas%20to%20find%20a%20value%20in%20a%20column%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270645%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20several%20problems%20with%20creating%20a%20formula.%3C%2FP%3E%3CP%3EI%20am%20performing%20a%20pairwise%20comparison%20(eg.%20Criteria%201%20vs%20Criteria%202%2C%203%2C%204...%20%2F%20Criteria%202%20vs%20Criteria%203%2C%204%2C%205...%20%2F%20Criteria%203%20vs%20Criteria%204%2C%205%2C%206%2C%20etc).%3C%2FP%3E%3CP%3E-%20Columns%20A%20and%20F%20have%20the%20weights%20of%20each%20criterion%20(naturally%2C%20there%20are%20duplicates%20values)%3C%2FP%3E%3CP%3E-%20The%20column%20%22H%22%20has%20the%20division%20of%20scores%20C%2FD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20create%20these%20two%20formulas%3A%3C%2FP%3E%3CP%3E1)%20I%20need%20to%20select%20the%20value%20in%20column%20%22H%22%20that%20meet%20two%20criteria%3A%20Correspond%20to%20the%20row%20of%20the%20Criteria%20in%20column%20%22B%22%20with%20the%20highest%20weight%20in%20%22A%22%20%3CSTRONG%3EPLUS%3C%2FSTRONG%3E%20Correspond%20to%20the%20row%20of%20the%20Criteria%20in%20column%20%22E%22%20with%20the%20SECOND%20highest%20weight%20in%20%22F%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20Criterion%204%20has%20the%20highest%20weight%20in%20column%20A%20and%20Criterion%208%20has%20the%20SECOND%20highest%20weight%20in%20Column%20F%2C%20I%20need%20a%20formula%20to%20automatically%20get%20that%201%2F2%20in%20column%20%22H%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%26nbsp%3BI%20need%20to%20select%20the%20value%20in%20column%20%22H%22%20that%20meet%20two%20criteria%3A%20Correspond%20to%20the%20row%20of%20the%20Criteria%20in%20column%20%22B%22%20with%20the%20SECOND%20highest%20weight%20in%20%22A%22%20%3CSTRONG%3EPLUS%3C%2FSTRONG%3E%20Correspond%20to%20the%20row%20of%20the%20Criteria%20in%20column%20%22E%22%20with%20the%20THIRD%20highest%20weight%20in%20%22F%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20task%20is%20very%20urgent%20and%20I%20would%20really%20appreciate%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ctorohuamanchumo_0-1618334901458.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F272558iD5ED32A9909BAC2C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ctorohuamanchumo_0-1618334901458.png%22%20alt%3D%22ctorohuamanchumo_0-1618334901458.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2270645%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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).

- 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!

 

ctorohuamanchumo_0-1618334901458.png

 

0 Replies