Lookup (not sure whether its X or V or H)

[swanbrown]

need help to find the result as per below. can someone help me please.

need help - excel.xlsx
	A	B	C	D	E	F	G					L	M	N	O	P
3	No	Employee Name	P Rating	Criticality	Result							Reference Table
4	1	MARCUS LIM	D	3								Criticality	P Rating
5	2	ELIZABETH TAYLOR	C	3									D	C	B	A
6	3	KEVIN COSTNER	B	3								1	D1	C1	B 1	A1
7	4	LIM AH LIM	A	3								2	D2	C2	B2	A2
8	5	ABDUL PATAH	D	2								3	D3	C3	B3	A3
9	6	LEE KUAN YEW	C	2
10	7	NG CHEE KANG	B	2
11	8	SADAM HUSSAIN	A	2
12	9	MOA TSE TUNG	D	1
13	10	Mohamad Ali	C	1
14	11	John Baker	B	1
15	12	John Doe	A	1
xlookup

 

[Peter_SSs]

Is this what you mean?

(If your profile is correct then you cannot use XLOOKUP as it does not exist in Excel 2016)

20 08 12.xlsm
	A	B	C	D	E	F	G					L	M	N	O	P
3	No	Employee Name	P Rating	Criticality	Result							Reference Table
4	1	MARCUS LIM	D	3	D3							Criticality	P Rating
5	2	ELIZABETH TAYLOR	C	3	C3								D	C	B	A
6	3	KEVIN COSTNER	B	3	B3							1	D1	C1	B 1	A1
7	4	LIM AH LIM	A	3	A3							2	D2	C2	B2	A2
8	5	ABDUL PATAH	D	2	D2							3	D3	C3	B3	A3
9	6	LEE KUAN YEW	C	2	C2
10	7	NG CHEE KANG	B	2	B2
11	8	SADAM HUSSAIN	A	2	A2
12	9	MOA TSE TUNG	D	1	D1
13	10	Mohamad Ali	C	1	C1
14	11	John Baker	B	1	B 1
15	12	John Doe	A	1	A1
INDEX MATCH

Cell Formulas
Range	Formula
E4:E15	E4	=INDEX(M$6:P$8,MATCH(D4,L$6:L$8,0),MATCH(C4,M$5:P$5,0))

 

[swanbrown]

thank you very much Peter. Appreciate this help! it works perfectly!

 

[Peter_SSs]

thank you very much Peter. Appreciate this help! it works perfectly!

You're welcome. Thanks for the follow-up.

 

[sykes]

There's probably a reason why not, but couldn't you just have used, in E4:
=C4&D4
... and filled that formula down...?
I can't see why a reference table's necessary, in this situation.

 

[Peter_SSs]

There's probably a reason why not, but couldn't you just have used, in E4:
=C4&D4
... and filled that formula down...?
I can't see why a reference table's necessary, in this situation.

With the actual sample data, I agree. I had assumed though that the sample reference table was not 'real'. Who knows?