check columns according to matrix

[Biopsy]

Hi!

I have attached a xls example cause than it is easier to understand.
I would need a formula please for the yellow highlighted column I that says the name either from column A or B depending on which value is bigger for the word in column C according to the matrix L1: DG11.
And that displays no name but "error" in the case that the values of the two names in column A and B are less than 10 points apart according to the matrix L1: DG11.

E.g. :
Row 3: Lisi (column A) has a smaller value than Christian (column B) for "autoritär" (Column C) according to the matrix (L1: DG11) , so "Christian" is displayed in column I.

Row 4: Maria (column A) has a value which is whithin 10 points to the value of Nora (column B) for "verletzlich" (Column C) according to the matrix (L1: DG11) , so "error" is displayed in column I.

Thank you very much!

[WideBoyDixon]

Try this in I3 and copy down:

Please Login or Register  to view this content.

It's long because of the "less than 10" condition. Could be simplified with helper columns.

WBD

[samba_ravi]

withdrawn, my formula is as copy as in post no 2

[Biopsy]

Thank you a lot, the formula works perfectly!!

I thought I could extend it to the same problem but with 4 instead of 2 comparison objects but I just realised that that is way too complex for me.
That's why I am asking for help once more and attach the file. It is the same idea but instead of comparing only column A and B, I have to compare Column A, B, C and D in respect of the word in column E according to the matrix R1: DM11.
Column K should display the name with the highest value, column L the name with the second highest value, column M the name with the third highest value and column N the name with the lowest value.
And again it would be great if "error" would be displayed in the case that the values of any of the four names in columns A, B, C and D are less than 10 points apart according to the matrix.

Thank you very much!

[WideBoyDixon]

I don't think you can achieve this level of complexity in a formula; trying to order 4 scores and deal with various criteria is going to need a UDF and for that you'll need to post a new thread in the VBA forum.

WBD

[samba_ravi]

if in 4 Checks difference between any two is less then 10 then what is your expected result for the 4 checks

[johnmpl]

Hi, to all!

It's not clear what is the results when the scores in any two is less than 10. Like samba_ravi says, you must give representative manual examples of what you should obtain.

For now, verify this approach:

1. When cell K3 is selected, create the following formulated name:
fScores : =SUMIF($R$2:$R$11,$A3:$D3,INDEX($S$2:$DM$11,,MATCH($E3,$S$1:$DM$1,)))-COLUMN($A3:$D3)%

2. Then, use this formula in K3:
=INDEX($A3:$D3,MATCH(LARGE(fScores,COLUMNS($K3:K3)),fScores,))
And drag it right and down.

The formulas avoid duplicates. Check file with the result. Blessings!

[Biopsy]

Hi to all!
So sorry for my late response, I havent been able to work for quite a while now unfortunatley.

Thank you very much for the formulas, they work very well. But just for my understanding and if I ll ever need to adapt something, where is the first one to define the fScores defined? (Sorry if that s a stupid question but I have never worked with such a formula)

Also very sorry for beiing not precise enough. If the scores in any two is less than 10, the result should already be "error".

Thank you very much!

[JeteMc]

To find the formula that defines fScores go to the Formulas tab > Defined Names pane > select Name Manager.
Let us know if you have any questions.

[Biopsy]

Thank you! Now I have understood it!
Thanks for your great help!