Vlook up and COnditions

[niting]

Hie forum,


Stuck up in a problem!!!


Have a list like dis-:


Col A Col B Col C Col D Col E

SrNo. Dependants Emp No Relation Age


1 71 Self 54

1 Wife 48

2 Son 24

2 67 Self 48

3 63 Self 45

1 Wife 40

2 Daughter 20

3 Mother 65


In this manner, the list proceeds for hundreds of employees and dependants.

The value in Column F corrosponding to the column where employee number is mentioned is picked up from another sheet with a formula :

=IF(ISBLANK(C8),"",VLOOKUP(e8,Sheet1!$D$3:$E$13,2,1))


However, the problem that i am facing is that, wherever formula picks the value for the employee , the cells below the employee for the dependents should show 20% of the value for that particular employee.


I had read sometime back in the forum that this thing can be achieved by using Count if. Unfortunately i am not able to locate the post.


Could some one help me with the formula to use???


Thanks

Nitin G

 

[Luke M]

You're table got screwed up a little bit in the post, so I may be interpreting it wrong. I think the issue boils down to if something (the emp #?) repeats, you want it to be 20% of the value from the VLOOKUP.


If that's correct, perhaps something like:

=IF(ISBLANK(C2),"",IF(COUNTIF(e$2:e2,e2)>0,0.2,1)*VLOOKUP(e2,Sheet1!$D$3:$E$13,2,1))


Does that help?

 

[niting]

Luke,


Hie!!


Column A contains the Serial No. of employees, column B contains the count of number of dependants, column C is the employee number, column D is the relationship of employee with the dependants( for relationship with himself, self is mentioned) and column E is the age.


Now, say an employee is there, then Serial no. 1 is mentioned in cell A1, cell B1 is left blank, cell C1 mentions the employee number, cell D1 mentions self and cell E1 mentions age, say 25.


Cell A2 is blank, Cell B2 contains the count of dependant of employee number mentioned in C1,cell C2 is blank, cell D2 mentions relationship eg wife, cell E2 mentions age of relation say 35.

In this manner, Cell B3, B4 contains the dependants count and relevant details for them in cell D3, D4 and cell E3 and E4.


This then moves to next employee with entry coming in Column A and Column C and their dependants details(is any) mentioned in row below the entry in A and C column.


The value in Cell F1 is picked thru the vlookup formula as mentioned in the original post.i.e.

=IF(ISBLANK(C1),"",VLOOKUP(e1,Sheet1!$D$3:$E$13,2,1))


What i want to achieve is that if there is value in cell C1, then the value be picked up from another sheet as per the entry in cell E1 and be populated in cell F1 and also the value in cells F2 , F3 be 20% of value in cell F1. When the next value comes in Column C for another employee, then the value in Col F, changes accordingly as per the vlookup sheet and the value for the dependants for the new employees becomes 20% of the value for the new employee.


I hope I have been able to explain my problem. Thanks for your prompt reply. I hope you have an answer to my problem.


Thanks

 

[Luke M]

With your example, in F2:

=IF(ISBLANK(C2),INDEX(F:F,MATCH(9E+99,$C$1:C1))*0.2,VLOOKUP(e1,Sheet1!$D$3:$E$13,2,1))