lookup

[rduke]

I have the following table


Table A
TN ECC 12
TN NTL 7
TN PORTLAND 12
TN CANAPORT 12
TN USAC 16
TN HFX/Pt.Tupper 9.5
WR ECC 14
WR PORTLAND 14
WR CANAPORT 14
WR USAC 18
WR NTL 8
WR HFX/Pt.Tupper 11
HIB NTL 6
HIB PORTLAND 6
HIB CANAPORT 6
HIB USAC 15
HIB ECC 11
HIB HFX/Pt.Tupper 9



In another table I have the following

Cargo Days Port Total
TN11 5 NTL
WR05 5 Portland
HIB23 5 Canaport



In the total column I need excel to look and see if the cargo number starts with TN, or WR, or HIB and then check the Port in table A and then assign the right number from Table A

Help

 

[CharlesChuckieCharles]

Sumifs (2007)

=sumifs(c1:c18,a1:a18,left(l2,2),b1:b18,n2)

 

[Jared_8878]

if your tables are in
A1:C18


and H1:K4


then in D1 I would put in a helper column of A1&B1

then for your totals in k2:k4 do

in k2 put in =OFFSET($D$1,MATCH(LEFT(H2,IFERROR(IF(FIND("HIB",H2),3,2),2))&J2,D1:D18,0)+1,-1)

and copy down. Not very robust but works for the given data.

 

[jasonb75]

Works with all versions of excel, no helpers needed.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Table A				Cargo	Days	Port	Total
2	TN	ECC	12		TN11	5	NTL	7
3	TN	NTL	7		WR05	5	Portland	14
4	TN	PORTLAND	12		HIB23	5	Canaport	6
5	TN	CANAPORT	12
6	TN	USAC	16
7	TN	HFX/Pt.Tupper	9.5
8	WR	ECC	14
9	WR	PORTLAND	14
10	WR	CANAPORT	14
11	WR	USAC	18
12	WR	NTL	8
13	WR	HFX/Pt.Tupper	11
14	HIB	NTL	6
15	HIB	PORTLAND	6
16	HIB	CANAPORT	6
17	HIB	USAC	15
18	HIB	ECC	11
19	HIB	HFX/Pt.Tupper	9
Sheet5

 

[rduke]

Almost there, THX. I need the value you got in H2 added to the value in F2

 

[jasonb75]

=f2+sumproduct(--isnumber(find($a$2:$a$19,e2)),--($b$2:$b$19=g2),$c$2:$c$19)

 

[rduke]

Thx