vlookup and match function

[RAJESH1960]

Hi, I know how to vlookup one value and match but I am trying to lookup and match the values of both the columns in data 1 with data 2 with the help of a formula. If both the values have an exact match then I want to display the name in data2 in column F or display “Not Found”.

qyerrt.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Data 1		Formula			Data 2
2				A	18				January	1	R
3				B	19				February	2	Q
4				C	16				March	3	P
5				D	15				April	4	O
6				E	14				May	5	N
7				F	13				June	6	M
8				G	11				July	7	L
9				H	11				August	8	K
10				I	10				September	9	J
11				J	9				October	10	I
12				K	8				November	11	H
13				L	5				December	12	G
14				M	6				January	13	F
15				N	5				February	14	E
16				O	4				March	15	D
17				P	2				April	16	C
18				Q	2				May	17	B
19				R	1				June	18	A
Sheet1

 

[KRice]

Below is one approach. The array formed will find multiple instances where the matches occur, but the "1" returns the one whose row number is smallest. If there might be multiple matches and you want all of the matches, then some adjustments will be necessary.

MrExcel20210129.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1				Data 1		Formula			Data 2
2				A	18	June			January	1	R
3				B	19				February	2	Q
4				C	16	April			March	3	P
5				D	15	March			April	4	O
6				E	14	February			May	5	N
7				F	13	January			June	6	M
8				G	11				July	7	L
9				H	11	November			August	8	K
10				I	10	October			September	9	J
11				J	9	September			October	10	I
12				K	8	August			November	11	H
13				L	5				December	12	G
14				M	6	June			January	13	F
15				N	5	May			February	14	E
16				O	4	April			March	15	D
17				P	2				April	16	C
18				Q	2	February			May	17	B
19				R	1	January			June	18	A
Sheet2

Cell Formulas
Range	Formula
F2:F19	F2	=IFERROR(INDEX($I$2:$I$19,AGGREGATE(15,6,(ROW($I$2:$I$19)-ROW($I$1))/(($K$2:$K$19=D2)*($J$2:$J$19=E2)),1)),"")

 

[RAJESH1960]

Below is one approach. The array formed will find multiple instances where the matches occur, but the "1" returns the one whose row number is smallest. If there might be multiple matches and you want all of the matches, then some adjustments will be necessary.

MrExcel20210129.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1				Data 1		Formula			Data 2
2				A	18	June			January	1	R
3				B	19				February	2	Q
4				C	16	April			March	3	P
5				D	15	March			April	4	O
6				E	14	February			May	5	N
7				F	13	January			June	6	M
8				G	11				July	7	L
9				H	11	November			August	8	K
10				I	10	October			September	9	J
11				J	9	September			October	10	I
12				K	8	August			November	11	H
13				L	5				December	12	G
14				M	6	June			January	13	F
15				N	5	May			February	14	E
16				O	4	April			March	15	D
17				P	2				April	16	C
18				Q	2	February			May	17	B
19				R	1	January			June	18	A
Sheet2

Cell Formulas
Range	Formula
F2:F19	F2	=IFERROR(INDEX($I$2:$I$19,AGGREGATE(15,6,(ROW($I$2:$I$19)-ROW($I$1))/(($K$2:$K$19=D2)*($J$2:$J$19=E2)),1)),"")

Thank You KRice. I just replaced "" with " Not Found". It's perfect. Thanks once again.

 

[KRice]

You're welcome...glad to help.