how to search the required criteria using Index match with a given condition

[supraman]

Hi Team,

Please help me with the below issue, in my excel file sheet 1 i have , where i need to identify the seller name from the Sheet 2 data given below.

I tried with Index match to get the seller name but failed to do as i am unable to use If condition in my array, to look for selected from the D column (nomination selected) , any different method to get rid of this issue, please suggest.

Sheet 1

Product	Requirement Received date	Selected Seller name
SAP	1/1/2018
SQL	1/15/2021
Temenos	5/12/2021

Sheet 2

Product	Seller name	Requirement Received date	Nomination selected
SAP	Max	1/1/2018	Selected
SAP	Jean	1/1/2018	Rejected
Python	Phillip	12/1/2019	Rejected
Python	Sovit	12/1/2019	Rejected
Python	Puneet	12/1/2019	Selected
SQL	Steave	1/15/2021	Rejected
SQL	Michel	1/1/2021	Rejected
SQL	Paul	1/15/2021	Selected
SQL	Shree	1/1/2021	Selected
Temenos	Jecab	5/12/2021	Rejected
Temenos	Phill	2/2/2021	Selected
Temenos	Samual	2/2/2021	Rejected
Temenos	Coumar	5/12/2021	Selected

 

[DanteAmor]

Try this:

Dante Amor
	A	B	C
1	Product	Requirement Received date	Selected Seller name
2	SAP	01/01/2018	Max
3	SQL	15/01/2021	Paul
4	Temenos	12/05/2021	Coumar
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=INDEX(Sheet2!$B$2:$B$14,SUMPRODUCT((Sheet2!$A$2:$A$14=A2)*(Sheet2!$C$2:$C$14=B2)*(Sheet2!$D$2:$D$14="Selected")*ROW(Sheet2!$B$2:$B$14))-ROW(Sheet2!$B$2)+1)

 

[supraman]

Thank you @Dante Amor.

 

[supraman]

Try this:

Dante Amor
	A	B	C
1	Product	Requirement Received date	Selected Seller name
2	SAP	01/01/2018	Max
3	SQL	15/01/2021	Paul
4	Temenos	12/05/2021	Coumar
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=INDEX(Sheet2!$B$2:$B$14,SUMPRODUCT((Sheet2!$A$2:$A$14=A2)*(Sheet2!$C$2:$C$14=B2)*(Sheet2!$D$2:$D$14="Selected")*ROW(Sheet2!$B$2:$B$14))-ROW(Sheet2!$B$2)+1)

Thanks a ton... you make it so simple for me... i was trying many things but this simple array is awesome !! Appreciated man.. Cheers

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.