Need Help with a multi Table XLS lookup

I have an XLS with two tables (reports & practices)

in my practices tab I have three columns Product Line, Sub Product Line & Practice

in my reports tab column C is practices but is blank
column E is Product Line & Column F is Sub Product Line
 
I need a formula or lookup that will accommodate the following:
if report:E = practices:A and Practices:B is blank then Report:C should equal Practices:C
if report:F = Practices:B then Report:C should equal Practices:C
Matt PinkstonEnterprise ArchitectAsked:
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.

Andrew VelascoOperational Account Management & Data AnalystCommented:
Hi,

I am assuming this is the scenario you are looking for, however let me know if I'm wrong:
If Reports F2 = 5 and Practices B2 = 6, but Practices B3 = 5 then Reports C2 would equal Practices C3

Give this a try:
=IF(ISNA(VLOOKUP(F2,Practices!B:C,2,FALSE)),IF(ISNA(VLOOKUP(E2,Practices!A:C,3,FALSE)),"",IF(VLOOKUP(E2,Practices!A:C,2,FALSE)="",VLOOKUP(E2,Practices!A:C,3,FALSE),"")),VLOOKUP(F2,Practices!B:C,2,FALSE))
0

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
Glenn RayExcel VBA DeveloperCommented:
I'm restating to make sure I understand:

You have a sheet, "Practices" with a three-column table:
A: Product Line
B: Sub Product Line
C: Practice

You have another sheet, "Reports" with (at least) a six-column table:
A:
B:
C: Practice
D:
E: Product Line
F: Sub Product Line

You want a method to update the values in column C of the "Reports" sheet like so:
1) If there is a Product Line value found on the "Practices" sheet, but no Sub Product Line value exists, then return the Practices value for that Product Line on the "Practices" sheet.
2) If there is a Sub Product Line value found on the "Practices" sheet, then return the Practices value for that Sub Product Line on the "Practices" sheet.

Immediately there is an issue as to whether the Product Line and Sub Product Line values are dependent or mutually-exclusive.  That will have a significant bearing on the type of indexing formula needed to return the correct Practices value.  

If you can provide a small set of sample data - say 20-30 rows at most - for both the Reports and Practices sheets, I'm sure a solution can be provided very quickly.

Regards,
-Glenn
0
Matt PinkstonEnterprise ArchitectAuthor Commented:
I think that is spot on, the only adjustment may be that if there is no find then the field should be Other
0
Glenn RayExcel VBA DeveloperCommented:
You mean, that if no match is found, return "Other" as the result?
0
Glenn RayExcel VBA DeveloperCommented:
There is still an issue of whether PL's and SubPL's are mutually-exclusive.  See this example:
comparison of data typesThis also helps demonstrate why actual data (even if redacted) will help determine the best resolution.

-Glenn
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.