Lookup from two tables and sum results

[Tashat]

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.

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

 

[KOKOSEK]

Into B4:

=IFERROR(VLOOKUP($A4,$A$18:$B$25,2,0),0)+IFERROR(VLOOKUP($A4,$E$18:$F$23,2,0),0)

and drag it down.

 

[Tashat]

Edit: Ah yes I've got it to work!!! Thank you KOKOSEK!

 

[KOKOSEK]

Happy to help.

 

[Tashat]

Into B4:

=IFERROR(VLOOKUP($A4,$A$18:$B$25,2,0),0)+IFERROR(VLOOKUP($A4,$E$18:$F$23,2,0),0)

and drag it down.

Hi! This is brilliant thank you.

Do you know if there is a way of changing the formula so that cells with 0 are just empty/blank?

 

[KOKOSEK]

=If(And(A1<>"",A1>0),IFERROR(VLOOKUP($A4,$A$18:$B$25,2,0),0)+IFERROR(VLOOKUP($A4,$E$18:$F$23,2,0),0),"")