[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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
0
Matt Pinkston
Asked:
Matt Pinkston
  • 3
1 Solution
 
Andrew VelascoCommented:
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
 
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 PinkstonAuthor 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now