between two condition any one condition match then get the value

[Vishaal]

hi all,

we have the following two sheets

one is

Book1
	A	B	C	D	E
1
2		USA	01-02-2020	6740508	tet@gmail.com
3		AUS	01-02-2020	8521625	usto@gmail.com
4		IND	01-02-2020	0053313	pos@gmail.com
5		NEW	01-02-2020	2991119	spam@gmail.com
Sheet1

second is

Book1
	A	B	C
1
2	jk-556	7729900	can@gmail.com
3	jk-554	7729902	usto@gmail.com
4	jk-557	6740508	bhub@gmail.com
5	jk-552	0053313
Sheet2

we want to check if from sheet 2, b2 or c2 any one value match with Sheet1!B2:E5 then it will copy the value from column Sheet2!A

result sheet is

Book1
	A	B	C	D	E	F
1
2		USA	01-02-2020	6740508	tet@gmail.com	jk-557
3		AUS	01-02-2020	8521625	usto@gmail.com	jk-554
4		IND	01-02-2020	0053313	pos@gmail.com	jk-552
5		NEW	01-02-2020	2991119	spam@gmail.com
Sheet1

help pls

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F
1
2		USA	1/2/2020	6740508	tet@gmail.com	jk-557
3		AUS	1/2/2020	8521625	usto@gmail.com	jk-554
4		IND	1/2/2020	53313	pos@gmail.com	jk-552
5		NEW	1/2/2020	2991119	spam@gmail.com
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=IFERROR(INDEX(Sheet2!$A$2:$A$5,MATCH(D2,Sheet2!$B$2:$B$5,0)),IFERROR(INDEX(Sheet2!$A$2:$A$5,MATCH(E2,Sheet2!$C$2:$C$5,0)),""))

 

[Vishaal]

Thanks Eric W bro

but its matching blank also

 

[Eric W]

I don't understand what you mean. I assume you're getting some incorrect result. Please show an example and I'll look at it.

 

[Vishaal]

pls check the below sheets

Sheet1

1.JAN.20 TO 31.JULY2020.xls
	A	B	C	D
1
2
3	01-01-2020			AST52780
4	01-02-2020	38624350		AST52546
5	01-02-2020			AST52780
6	01-03-2020	80051229		AST52546
Contact Details

Sheet2

1.JAN.20 TO 31.JULY2020.xls
	A	B	C	D
1
2	AST52496	Item 1	84-2625004	sstm@gmail.org
3	AST52546	Item 2	523422
4	AST52780	Item 3
5	AST52809	Item 4		ddy12@gmail.com
Sheet1

its giving the wrong result.

help pls

 

[Eric W]

I'm more confused than ever. Neither of the sheets you show in post 5 matches the sheets you showed in post 1. Moreover, neither one shows the formula, with or without a correct result. I don't know what you want.

 

[Vishaal]

I'm more confused than ever. Neither of the sheets you show in post 5 matches the sheets you showed in post 1. Moreover, neither one shows the formula, with or without a correct result. I don't know what you want.

Sorry Eric W Bro,
i will check and update and take care this in future

i have just checked it and really sorry