Need Help with a multi Table XLS lookup

Posted on 2014-08-19
Last Modified: 2014-08-20
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
Question by:Matt Pinkston
    LVL 2

    Accepted Solution


    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:
    LVL 27

    Expert Comment

    by:Glenn Ray
    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:
    C: Practice
    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.


    Author Comment

    by:Matt Pinkston
    I think that is spot on, the only adjustment may be that if there is no find then the field should be Other
    LVL 27

    Expert Comment

    by:Glenn Ray
    You mean, that if no match is found, return "Other" as the result?
    LVL 27

    Expert Comment

    by:Glenn Ray
    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.


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    excel forecast function 1 27
    Sum Per Month 7 23
    Revise Excel macro to include missing information 4 19
    VBA Help TT V-1.1 15 22
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now