Lookup and if statements or index Match on several columns

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
I am looking for the correct syntax to lookup the value of the Salary for certain employees where Resource Type = "Salaried/W-2"

if the Names match, same date and resource type is correct then return the salary value / 12 months.
this what I have so far.

=IF([@Resource]=SalaryDetail_Data!A:A,IF([@Dates]=SalaryDetail_Data!C:C,IF([@ResourceType]="Salaried/W-2",SalaryDetail!D:D)/12))

What am I missing?

K
sample.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Your sample workbook does not match the formula you say isn't working. I tried to patch it (the workbook) up, and am using the following formula:
=IFERROR(IF([@ResourceType]<>"Salaried/W-2","",LOOKUP(2,1/(([@Resource]=SalaryDetail_Data!A:A)*([@Dates]=SalaryDetail_Data!C:C)),SalaryDetail_Data!D:D)/12),"")

If the above formula doesn't work in your real workbook, could you please make a Table where you want the formula to go, and add a worksheet SalaryDetail_Data so I can test the looking up.

Note that if a name occurs more than once in SalaryDetail_Data, the last salary found will be returned.
sample-1Q28929389.xlsm
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
By way of explanation, the LOOKUP function can be entered like a regular formula, but it behaves like an array-entered formula. It is like AGGREGATE and SUMPRODUCT in this respect.

LOOKUP is being asked to find the value 2, but the array it is searching will only ever equal 1 or #DIV/0! error value. The error value results when at least one of the tests (for resource name and dates) fail. The value of 1 results (for a given row of data), when both the tests for name and date succeed.
1/(([@Resource]=SalaryDetail_Data!A:A)*([@Dates]=SalaryDetail_Data!C:C))

LOOKUP has another interesting property in that it ignores error values and seeks a match only for values of the same data type (number or text) as its first parameter. So by looking for 2, it will find a match for all the rows where the name & date are a match. LOOKUP then returns a value from column D on the last such row.

When testing the formula, I noticed an annoying delay for recalculation. This is because the Table in my test worksheet extends to row 1048576. You will get much snappier recalcs if you shorten the Table to a more manageable size.
Karen SchaeferBI ANALYST

Author

Commented:
thanks that did the trick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial