Lookup with Multiple Conditions

**anilswarrier** · 11-20-2012, 11:48 AM

Hello...
I am preparing an excel program for displaying the employees on leave today. Sheet "Master" is for entering the employee particulars and sheet "Data" for entering the leave particulars. Employees who are on leave today, should display in sheet"Today". Can you please provide a formula for displaying Branch and Designation. In this example, branch should be Bangalore and designation should be Chief Manager.(During this period, he is in Bangalore and working as Chief Manager.) The file is attached.

Can anybody provide a formula/macro/vba...
Thanks in advance

Anil

**kbkumar** · 11-20-2012, 12:02 PM

Hi - Ideally Emp_No filed would be unique in most org's. Going by that concept your existing formulas should work.

If for some reason the Emp_No's are not different then you may create a "helper" column and combine Emp_No and Name (preferably in first column i.e. Column A) and then use lookup's on cells from this column.

Does that help?

**anilswarrier** · 11-20-2012, 01:21 PM

Emp_No is unique, but branch name (on transfer) and designation (on pomotion) may change. The branch name BIC (branch code) and designation should be in accordance with hte dates of leave.

**kbkumar** · 11-20-2012, 01:29 PM

And if you combine Emp_No and Name using helper column does that help you getting unique values where you can imply your lookup's?

**anilswarrier** · 11-20-2012, 01:38 PM

I mean, in my sample file, Sheet "Today", Mr.B has taken leave from 11/11/2012 to 25/11/2012 (Refer sheet "Data") and in that period, Mr.B is working in Bangalore as Chief Manager (Refer sheet "Master"). But it is displaying Branch as MUMBAI and Designation as Branch Manager - I. ie., the first row containing the Emp_No of B.