Matt Pinkston
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think that is spot on, the only adjustment may be that if there is no find then the field should be Other
You mean, that if no match is found, return "Other" as the result?
There is still an issue of whether PL's and SubPL's are mutually-exclusive. See this example:
This also helps demonstrate why actual data (even if redacted) will help determine the best resolution.
-Glenn
This also helps demonstrate why actual data (even if redacted) will help determine the best resolution.-Glenn
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