Hi All
I would really appreciate some help with a formula. I need to perform vlookup (I think) which looks up values in two lookup tables and sums the total. I have a main table with hundreds of locations and two lookup tables where there are some locations in one and some locations in the other. Some are repeated (e.g. location 1 appears in both tables) and others only appear in one or the other lookup table (e.g. Location 2 only appears in lookup table 1 and location 4 only appears in lookup table 2). I have included a simple example below with the totals showing in B4:B11 as I want them to appear, but I can't do this manually for my actual table which has hundreds of rows. I also don't want an error to return if the location can't be found in one of the tables. If possible I'd like to avoid using macros. Can anyone help?
Many thanks in advance.
I would really appreciate some help with a formula. I need to perform vlookup (I think) which looks up values in two lookup tables and sums the total. I have a main table with hundreds of locations and two lookup tables where there are some locations in one and some locations in the other. Some are repeated (e.g. location 1 appears in both tables) and others only appear in one or the other lookup table (e.g. Location 2 only appears in lookup table 1 and location 4 only appears in lookup table 2). I have included a simple example below with the totals showing in B4:B11 as I want them to appear, but I can't do this manually for my actual table which has hundreds of rows. I also don't want an error to return if the location can't be found in one of the tables. If possible I'd like to avoid using macros. Can anyone help?
Many thanks in advance.
Mr_Excel_Query_Two_Lookups.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Main Table | ||||||||
2 | |||||||||
3 | Location Name | Total | |||||||
4 | Location 1 | 6 | |||||||
5 | Location 2 | 4 | |||||||
6 | Location 3 | 2 | |||||||
7 | Location 4 | 3 | |||||||
8 | Location 5 | 2 | |||||||
9 | Location 6 | 10 | |||||||
10 | Location 7 | 4 | |||||||
11 | Location 8 | 1 | |||||||
12 | |||||||||
13 | |||||||||
14 | |||||||||
15 | |||||||||
16 | Lookup table 1 | Lookup table 2 | |||||||
17 | |||||||||
18 | Location Name | Value | Location Name | Value | |||||
19 | Location 1 | 2 | Location 1 | 4 | |||||
20 | Location 2 | 4 | Location 3 | 1 | |||||
21 | Location 3 | 1 | Location 4 | 3 | |||||
22 | Location 5 | 2 | Location 6 | 4 | |||||
23 | Location 6 | 6 | Location 7 | 1 | |||||
24 | Location 7 | 3 | |||||||
25 | Location 8 | 1 | |||||||
26 | |||||||||
27 | |||||||||
Sheet1 |