Vlookup and if condition

[raviteja26187]

Hi,

I have 2 sheets as below

Sheet 1

empid <tbody> </tbody>	dependent_name <tbody> </tbody>	relation_with_self <tbody> </tbody>	dep_id <tbody> </tbody>
40100317 <tbody> </tbody>	Kanhaiya Lal Srivastava <tbody> </tbody>	FATHER <tbody> </tbody>	161532 <tbody> </tbody>

<tbody>
</tbody>

Sheet 2

empid <tbody> </tbody>	dep_name <tbody> </tbody>	RELATIONDESCRIPTION <tbody> </tbody>	dependent_id <tbody> </tbody>
40100317 <tbody> </tbody>	Priya Srivastava <tbody> </tbody>	WIFE <tbody> </tbody>	161535 <tbody> </tbody>
40100317 <tbody> </tbody>	Aniket Srivastava <tbody> </tbody>	SON <tbody> </tbody>	161534 <tbody> </tbody>
40100317 <tbody> </tbody>	Chetna Srivastav <tbody> </tbody>	DAUGHTER <tbody> </tbody>	161531 <tbody> </tbody>
40100317 <tbody> </tbody>	Kanhaiya Lal Srivastava <tbody> </tbody>	FATHER <tbody> </tbody>	161532 <tbody> </tbody>
40100317 <tbody> </tbody>	Nirmala Srivastava <tbody> </tbody>	SISTER <tbody> </tbody>	161533 <tbody> </tbody>

<tbody>
</tbody>

I would like to Fetch dependent_id in Sheet2 by matching Relationdescription and Empid

Can any one please help me.


Regards,
Ravimbb

 

[Aladin Akyurek]

In D2 of Sheet1, control+shift+enter, not just enter:

=INDEX(Sheet2!$D$2:$D$6,MATCH($C2,IF($A2=Sheet2!$A$2:$A$6,Sheet2!$C$2:$C$6),0))

Wrap this into an IFERROR or IFNA call, if you don't want to see #N/A errors.

 

[raviteja26187]

Hi Aladin,

Thanks for the great help, you saved my day. working perfectly..Thank you very much

 

[Aladin Akyurek]

Hi Aladin,

Thanks for the great help, you saved my day. working perfectly..Thank you very much

You are welcome.

 

[raviteja26187]

Hi,

Can you please help me to fetch the sister details, in the below example i have added 2 sisters(Sue and Catherine) for the employee.


I have 2 sheets as below

Sheet 1

empid <tbody> </tbody>	dependent_name <tbody> </tbody>	relation_with_self <tbody> </tbody>	dep_id <tbody> </tbody>
40100317 40100317 40100317 <tbody> </tbody>	Kanhaiya Lal Srivastava Sue Catherine <tbody> </tbody>	FATHER SISTER SISTER <tbody> </tbody>	161532 168522 169582 <tbody> </tbody>

<tbody>
</tbody>

Sheet 2

empid <tbody> </tbody>	dep_name <tbody> </tbody>	RELATIONDESCRIPTION <tbody> </tbody>	dependent_id <tbody> </tbody>
40100317 <tbody> </tbody>	Priya Srivastava <tbody> </tbody>	WIFE <tbody> </tbody>	161535 <tbody> </tbody>
40100317 <tbody> </tbody>	Aniket Srivastava <tbody> </tbody>	SON <tbody> </tbody>	161534 <tbody> </tbody>
40100317 <tbody> </tbody>	Chetna Srivastav <tbody> </tbody>	DAUGHTER <tbody> </tbody>	161531 <tbody> </tbody>
40100317 <tbody> </tbody>	Kanhaiya Lal Srivastava <tbody> </tbody>	FATHER <tbody> </tbody>	161532 <tbody> </tbody>
40100317 40100317 40100317 <tbody> </tbody>	Nirmala Srivastava Sue Catherine <tbody> </tbody>	SISTER SISTER SISTER <tbody> </tbody>	161533 168522 169582 <tbody> </tbody>

<tbody>
</tbody>

I would like to Fetch dependent_id in Sheet2 by matching Relation description and Empid

Please help me to fetch sister details.



Regards,
Ravimbb[/QUOTE]

 

[Aladin Akyurek]

Hi,

Can you please help me to fetch the sister details, in the below example i have added 2 sisters(Sue and Catherine) for the employee.

[...]

In D2 of Sheet1 control+shift+enter and copy down:

=INDEX(Sheet2!$D$2:$D$8,MATCH($C2,IF($A2=Sheet2!$A$2:$A$8,IF($B2=Sheet2!$B$2:$B$8,Sheet2!$C$2:$C$8)),0))