Index and Match

[swanbrown]

Hi, i have this employee spreadsheet where i need to find the performance potential based on several criteria i.e. performance rating, attitude and competency.

No	Employee Name	Performance Rating	Attitude	Competencies	Performance Potential
1	MARCUS LIM	A	3	1	?
2	ELIZABETH TAYLOR	A+	1	2
3	KEVIN COSTNER	A-	3	1
4	LIM AH LIM	B	3	3
5	ABDUL PATAH	C+	1	4
6	LEE KUAN YEW	C-	1	1
7	NG CHEE KANG	B-	2	4
8	SADAM HUSSAIN	B-	4	3
9	MOA TSE TUNG	B-	4	4

below is the table reference :

 

[kweaver]

Welcome to MrExcel.
Please provide how you would determine the Performance Potential for those listed -- what's the criteria?
It would also help if you used XL2BB to show your data.

 

[swanbrown]

thank you very much for responding.

I am trying to formulate as per example below:

If Marcus Lim has the following:
Performance Rating - A
Attitude - 3
Competencies - 1

based on the table reference above, his performance potential would be B.

I want to attach the sample excel spreadsheet so that it is clearer but i dont know how to.

 

[kweaver]

Could this be it?

Book3
	A	B	C	D	E	F	G	H	I	J
1		Employee	Perf Rate	Attitude	Compet	Perf Pot	Attitude	Compet	Perf Rate	Perf Pot
2		Marcus	A	3	1	B	1	1	A+, A, A-	A
3		Elizabeth	A+	1	1	A	1	2	A+, A, A-	A
4		Kevin	A-	3	1	B	2	1	A+, A, A-	A
5		Lim	B	3	3	Error	2	2	A+, A, A-	A
6		Abdul	C+	1	4	Error	1	1	B+	A
7		Lee	C-	1	1	C	3	1	A+, A, A-	B
8		Ng	B-	2	4	D	3	2	A+, A, A-	B
9		Sadam	B-	4	3	Error	1	1	B+	B
10		Moa	B-	4	4	Error	1	2	B+, B	B
11							1	3	B+, B	B
12							2	1	B+, B	B
13							3	1	B+, B	C
14							2	1	C+	C
15							2	2	C+	C
16							1	1	C, C-, D, D-	C
17							2	4	B-	D
18							3	4	C+	D
19							4	1	C+	D
20							4	2	C+	D
21							4	3	C+	D
22							4	4	C+	D
23							1	2	C, C-, D, D-	D
24							4	4	C, C-, D, D-	D
Sheet1

Cell Formulas
Range	Formula
F2:F10	F2	=IFERROR(INDEX($J$2:$J$24,SUM(ISNUMBER(SEARCH(C2&",",$I$2:$I$24&","))*(D2=$G$2:$G$24)*(E2=$H$2:$H$24)*ROW($2:$24))-1,1),"Error")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[swanbrown]

hi thank you so much.

Row 5 - Lim result for performance potential should be C. perhaps i show you the full table reference as per below:

Attitude	Competence	Performance Rating	Performance Potential
1	1	A+, A, A-	A
1	2	A+, A, A-	A
2	1	A+, A, A-	A
2	2	A+, A, A-	A
1	1	B+	A
3	1	A+, A, A-	B
3	2	A+, A, A-	B
1	1	B	B
1	2	B+, B	B
1	3	B+, B	B
2	1	B+, B	B
2	2	B+, B	B
2	3	B+, B	B
3	1	B+, B	B
3	2	B+, B	B
1	2	B-	B
2	1	B-	B
2	2	B-	B
3	3	B+, B	C
4	1	B+, B	C
4	2	B+, B	C
1	3	B-	C
1	4	B-	C
2	3	B-	C
3	1	B-	C
3	2	B-	C
4	1	B-	C
4	2	B-	C
1	1	C+	C
1	2	C+	C
1	3	C+	C
1	4	C+	C
2	1	C+	C
2	2	C+	C
1	1	C, C-, D, D-	C
2	4	B-	D
3	3	B-	D
3	4	B-	D
4	3	B-	D
4	4	B-	D
2	3	C+	D
2	4	C+	D
3	1	C+	D
3	2	C+	D
3	3	C+	D
3	4	C+	D
4	1	C+	D
4	2	C+	D
4	3	C+	D
4	4	C+	D
1	2	C, C-, D, D-	D
1	3	C, C-, D, D-	D
1	4	C, C-, D, D-	D
2	1	C, C-, D, D-	D
2	2	C, C-, D, D-	D
2	3	C, C-, D, D-	D
2	4	C, C-, D, D-	D
3	1	C, C-, D, D-	D
3	2	C, C-, D, D-	D
3	3	C, C-, D, D-	D
3	4	C, C-, D, D-	D
4	1	C, C-, D, D-	D
4	2	C, C-, D, D-	D
4	3	C, C-, D, D-	D
4	4	C, C-, D, D-	D
1	3	A+, A, A-	Error
2	3	A+, A, A-	Error
1	1	B-	Error
1	4	A+, A, A-	Error
2	4	A+, A, A-	Error
3	3	A+, A, A-	Error
4	1	A+, A, A-	Error
4	2	A+, A, A-	Error
1	4	B+, B	Error
2	4	B+, B	Error
3	4	A+, A, A-	Error
4	3	A+, A, A-	Error
4	4	A+, A, A-	Error
3	4	B+, B	Error
4	3	B+, B	Error
4	4	B+, B	Error

 

[kweaver]

With the full table, my array formula returns a C for Lim. Replace (in the F column) the 24 to an 82 when you put in the entire table.
If your table is on another sheet, you'll have to change the reference accordingly.

Book3
	B	C	D	E	F
1	Employee	Perf Rate	Attitude	Compet	Perf Pot
2	Marcus	A	3	1	B
3	Elizabeth	A+	1	1	A
4	Kevin	A-	3	1	B
5	Lim	B	3	3	C
6	Abdul	C+	1	4	C
7	Lee	C-	1	1	C
8	Ng	B-	2	4	D
9	Sadam	B-	4	3	D
10	Moa	B-	4	4	D
11	Test	C+	4	3	D
12	Test2	D	1	2	D
13	Test3	D	1	1	C
14	Test4	B+	3	1	B
15	Test5	A	1	3	Error
Sheet1

Cell Formulas
Range	Formula
F2:F15	F2	=IFERROR(INDEX($J$2:$J$82,SUM(ISNUMBER(SEARCH(C2&",",$I$2:$I$82&","))*(D2=$G$2:$G$82)*(E2=$H$2:$H$82)*ROW($2:$82))-1,1),"Error")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[swanbrown]

oh my god... thank you soooo much. this really helps. i have been trying to solve this for weeks. had migraines too. i am sooo thankful.

 

[swanbrown]

i am sorry to have to bother you again. i copied your formula and data into my excel. but the results for Perf Pot does not return as expected. below is the exact sheet

						Attitude	Competence	Performance Rating	Performance Potential
	Employee	Perf Rate	Attitude	Compet	Perf Pot	1	1	A+, A, A-	A
	Marcus	A	3	1	Error	1	2	A+, A, A-	A
	Elizabeth	A+	1	1	Error	2	1	A+, A, A-	A
	Kevin	A-	3	1	Error	2	2	A+, A, A-	A
	Lim	B	3	3	Error	1	1	B+	A
	Abdul	C+	1	4	Error	3	1	A+, A, A-	B
	Lee	C-	1	1	Error	3	2	A+, A, A-	B
	Ng	B-	2	4	Error	1	1	B	B
	Sadam	B-	4	3	Error	1	2	B+, B	B
	Moa	B-	4	4	Error	1	3	B+, B	B
	Test	C+	4	3	Error	2	1	B+, B	B
	Test2	D	1	2	Error	2	2	B+, B	B
	Test3	D	1	1	Error	2	3	B+, B	B
	Test4	B+	3	1	A	3	1	B+, B	B
	Test5	A	1	3	Error	3	2	B+, B	B
						1	2	B-	B
				this is the formula i keyed for perf pot	=IFERROR(INDEX($J$2:$J$82,SUM(ISNUMBER(SEARCH(C3&",",$I$2:$I$82&","))*(D3=$G$2:$G$82)*(E2=$H$2:$H$82)*ROW($2:$82))-1,1),"Error")	2	1	B-	B
						2	2	B-	B
						3	3	B+, B	C
						4	1	B+, B	C
						4	2	B+, B	C
						1	3	B-	C
						1	4	B-	C
						2	3	B-	C
						3	1	B-	C
						3	2	B-	C
						4	1	B-	C
						4	2	B-	C
						1	1	C+	C
						1	2	C+	C
						1	3	C+	C
						1	4	C+	C
						2	1	C+	C
						2	2	C+	C
						1	1	C, C-, D, D-	C
						2	4	B-	D
						3	3	B-	D
						3	4	B-	D
						4	3	B-	D
						4	4	B-	D
						2	3	C+	D
						2	4	C+	D
						3	1	C+	D
						3	2	C+	D
						3	3	C+	D
						3	4	C+	D
						4	1	C+	D
						4	2	C+	D
						4	3	C+	D
						4	4	C+	D
						1	2	C, C-, D, D-	D
						1	3	C, C-, D, D-	D
						1	4	C, C-, D, D-	D
						2	1	C, C-, D, D-	D
						2	2	C, C-, D, D-	D
						2	3	C, C-, D, D-	D
						2	4	C, C-, D, D-	D
						3	1	C, C-, D, D-	D
						3	2	C, C-, D, D-	D
						3	3	C, C-, D, D-	D
						3	4	C, C-, D, D-	D
						4	1	C, C-, D, D-	D
						4	2	C, C-, D, D-	D
						4	3	C, C-, D, D-	D
						4	4	C, C-, D, D-	D
						1	3	A+, A, A-	Error
						2	3	A+, A, A-	Error
						1	1	B-	Error
						1	4	A+, A, A-	Error
						2	4	A+, A, A-	Error
						3	3	A+, A, A-	Error
						4	1	A+, A, A-	Error
						4	2	A+, A, A-	Error
						1	4	B+, B	Error
						2	4	B+, B	Error
						3	4	A+, A, A-	Error
						4	3	A+, A, A-	Error
						4	4	A+, A, A-	Error
						3	4	B+, B	Error
						4	3	B+, B	Error
						4	4	B+, B	Error

 

[swanbrown]

i managed to download XL2BB. below is the sheet

need help.xlsx
	A	B	C	D	E	F	G	H	I	J
1							Attitude	Competence	Performance Rating	Performance Potential
2		Employee	Perf Rate	Attitude	Compet	Perf Pot	1	1	A+, A, A-	A
3		Marcus	A	3	1	Error	1	2	A+, A, A-	A
4		Elizabeth	A+	1	1	Error	2	1	A+, A, A-	A
5		Kevin	A-	3	1	Error	2	2	A+, A, A-	A
6		Lim	B	3	3	Error	1	1	B+	A
7		Abdul	C+	1	4	Error	3	1	A+, A, A-	B
8		Lee	C-	1	1	Error	3	2	A+, A, A-	B
9		Ng	B-	2	4	Error	1	1	B	B
10		Sadam	B-	4	3	Error	1	2	B+, B	B
11		Moa	B-	4	4	Error	1	3	B+, B	B
12		Test	C+	4	3	Error	2	1	B+, B	B
13		Test2	D	1	2	Error	2	2	B+, B	B
14		Test3	D	1	1	Error	2	3	B+, B	B
15		Test4	B+	3	1	A	3	1	B+, B	B
16		Test5	A	1	3	Error	3	2	B+, B	B
17							1	2	B-	B
18							2	1	B-	B
19							2	2	B-	B
20							3	3	B+, B	C
21							4	1	B+, B	C
22							4	2	B+, B	C
23							1	3	B-	C
24							1	4	B-	C
25							2	3	B-	C
26							3	1	B-	C
27							3	2	B-	C
28							4	1	B-	C
29							4	2	B-	C
30							1	1	C+	C
31							1	2	C+	C
32							1	3	C+	C
33							1	4	C+	C
34							2	1	C+	C
35							2	2	C+	C
36							1	1	C, C-, D, D-	C
37							2	4	B-	D
38							3	3	B-	D
39							3	4	B-	D
40							4	3	B-	D
41							4	4	B-	D
42							2	3	C+	D
43							2	4	C+	D
44							3	1	C+	D
45							3	2	C+	D
46							3	3	C+	D
47							3	4	C+	D
48							4	1	C+	D
49							4	2	C+	D
50							4	3	C+	D
51							4	4	C+	D
52							1	2	C, C-, D, D-	D
53							1	3	C, C-, D, D-	D
54							1	4	C, C-, D, D-	D
55							2	1	C, C-, D, D-	D
56							2	2	C, C-, D, D-	D
57							2	3	C, C-, D, D-	D
58							2	4	C, C-, D, D-	D
59							3	1	C, C-, D, D-	D
60							3	2	C, C-, D, D-	D
61							3	3	C, C-, D, D-	D
62							3	4	C, C-, D, D-	D
63							4	1	C, C-, D, D-	D
64							4	2	C, C-, D, D-	D
65							4	3	C, C-, D, D-	D
66							4	4	C, C-, D, D-	D
67							1	3	A+, A, A-	Error
68							2	3	A+, A, A-	Error
69							1	1	B-	Error
70							1	4	A+, A, A-	Error
71							2	4	A+, A, A-	Error
72							3	3	A+, A, A-	Error
73							4	1	A+, A, A-	Error
74							4	2	A+, A, A-	Error
75							1	4	B+, B	Error
76							2	4	B+, B	Error
77							3	4	A+, A, A-	Error
78							4	3	A+, A, A-	Error
79							4	4	A+, A, A-	Error
80							3	4	B+, B	Error
81							4	3	B+, B	Error
82							4	4	B+, B	Error
Sheet2

Cell Formulas
Range	Formula
F3:F16	F3	=IFERROR(INDEX($J$2:$J$82,SUM(ISNUMBER(SEARCH(C3&",",$I$2:$I$82&","))*(D3=$G$2:$G$82)*(E2=$H$2:$H$82)*ROW($2:$82))-1,1),"Error")

what am i doing wrong here please.

 

[Amit Tandon]

26Aug19.xlsx
	B	C	D	E	F	G	H	I	J	K
1	Employee	Perf Rate	Attitude	Compet	Perf Pot		Attitude	Competence	Performance Rating	Performance Potential
2	Marcus	A	3	1	B		1	1	A+, A, A-	A
3	Elizabeth	A+	1	1	A		1	2	A+, A, A-	A
4	Kevin	A-	3	1	B		2	1	A+, A, A-	A
5	Lim	B	3	3	C		2	2	A+, A, A-	A
6	Abdul	C+	1	4	C		1	1	B+	A
7	Lee	C-	1	1	C		3	1	A+, A, A-	B
8	Ng	B-	2	4	D		3	2	A+, A, A-	B
9	Sadam	B-	4	3	D		1	1	B	B
10	Moa	B-	4	4	D		1	2	B+, B	B
11	Test	C+	4	3	D		1	3	B+, B	B
12	Test2	D	1	2	D		2	1	B+, B	B
13	Test3	D	1	1	C		2	2	B+, B	B
14	Test4	B+	3	1	B		2	3	B+, B	B
15	Test5	A	1	3	Error		3	1	B+, B	B
16							3	2	B+, B	B
17							1	2	B-	B
18							2	1	B-	B
19							2	2	B-	B
20							3	3	B+, B	C
21							4	1	B+, B	C
22							4	2	B+, B	C
23							1	3	B-	C
24							1	4	B-	C
25							2	3	B-	C
26							3	1	B-	C
27							3	2	B-	C
28							4	1	B-	C
29							4	2	B-	C
30							1	1	C+	C
31							1	2	C+	C
32							1	3	C+	C
33							1	4	C+	C
34							2	1	C+	C
35							2	2	C+	C
36							1	1	C, C-, D, D-	C
37							2	4	B-	D
38							3	3	B-	D
39							3	4	B-	D
40							4	3	B-	D
41							4	4	B-	D
42							2	3	C+	D
43							2	4	C+	D
44							3	1	C+	D
45							3	2	C+	D
46							3	3	C+	D
47							3	4	C+	D
48							4	1	C+	D
49							4	2	C+	D
50							4	3	C+	D
51							4	4	C+	D
52							1	2	C, C-, D, D-	D
53							1	3	C, C-, D, D-	D
54							1	4	C, C-, D, D-	D
55							2	1	C, C-, D, D-	D
56							2	2	C, C-, D, D-	D
57							2	3	C, C-, D, D-	D
58							2	4	C, C-, D, D-	D
59							3	1	C, C-, D, D-	D
60							3	2	C, C-, D, D-	D
61							3	3	C, C-, D, D-	D
62							3	4	C, C-, D, D-	D
63							4	1	C, C-, D, D-	D
64							4	2	C, C-, D, D-	D
65							4	3	C, C-, D, D-	D
66							4	4	C, C-, D, D-	D
67							1	3	A+, A, A-	Error
68							2	3	A+, A, A-	Error
69							1	1	B-	Error
70							1	4	A+, A, A-	Error
71							2	4	A+, A, A-	Error
72							3	3	A+, A, A-	Error
73							4	1	A+, A, A-	Error
74							4	2	A+, A, A-	Error
75							1	4	B+, B	Error
76							2	4	B+, B	Error
77							3	4	A+, A, A-	Error
78							4	3	A+, A, A-	Error
79							4	4	A+, A, A-	Error
80							3	4	B+, B	Error
81							4	3	B+, B	Error
82							4	4	B+, B	Error
Sheet3

Cell Formulas
Range	Formula
F2:F15	F2	=INDEX(K$2:K$82,AGGREGATE(15,6,(ROW(K$2:K$82)-ROW(K$2)+1)/(ISNUMBER(SEARCH(C2&",",J$2:J$82&","))*(D2=H$2:H$82)*(E2=I$2:I$82)),1))