Solved

# Need Help with a multi Table XLS lookup

Posted on 2014-08-19
84 Views
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
Question by:Matt Pinkston

LVL 2

Accepted Solution

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

LVL 27

Expert Comment

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

Author Comment

I think that is spot on, the only adjustment may be that if there is no find then the field should be Other
0

LVL 27

Expert Comment

You mean, that if no match is found, return "Other" as the result?
0

LVL 27

Expert Comment

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
0

## Featured Post

### Suggested Solutions

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.