Lookup and if statements or index Match on several columns

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
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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
byundtMechanical EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks that did the trick
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.