Match 2 values in a table and output a figure if true

[baggers719]

I'm trying to lookup the value in column B and F in sheet1 and output the value in column I in sheet2 if these match in columns D and E in sheet2.


Sheet 1

B is Zone
F is Weight

Use this calculator for Pre-Paid DME Shipping Price Calculations
		Shipping Box Dimensions: (round to 2nd decimal point)
Customer Shipping Zip Code:	Zone:	Length	Width	Height	Dimensional Weight	Shipping Cost
34787	8	8.2	8.2	6.2	3	Not Available
34787	8	8.2	8.2	6.2	3	Not Available
79124	5	20	20	12	27	Not Available
34787	8	8.2	8.2	6.2	3	Not Available

Sheet2

D is Zone
E is Weight
I is cost

Standard	74​	FedEx SmartPost	1​	8​	5.8743	0​		$10.98​
Standard	74​	FedEx SmartPost	2​	8​	5.8743	0​		$10.98​
Standard	74​	FedEx SmartPost	3​	8​	6.3344	0​		$11.84​
Standard	74​	FedEx SmartPost	4​	8​	6.93895	0​		$12.97​
Standard	74​	FedEx SmartPost	5​	8​	7.4793	0​		$13.98​
Standard	74​	FedEx SmartPost	6​	8​	7.74145	0​		$14.47​
Standard	74​	FedEx SmartPost	7​	8​	8.2283	0​		$15.38​
Standard	74​	FedEx SmartPost	8​	8​	8.8382	0​		$16.52​
Standard	74​	FedEx SmartPost	9​	8​	20.20695	0​		$37.77​
Standard	74​	FedEx SmartPost	10​	8​	20.20695	0​		$37.77​

So I want to output cost (I) in Sheet1 where the the zone and cost match in sheet2. For example, shipping cost (column G) in sheet1 where zone is 8 and weight is 3 should read $11.84

 

[Fluff]

How about

+Fluff.xlsm
	A	B	C	D	E	F	G
1	Use this calculator for Pre-Paid DME Shipping Price Calculations
2			Shipping Box Dimensions: (round to 2nd decimal point)
3	Customer Shipping Zip Code:	Zone:	Length	Width	Height	Dimensional Weight	Shipping Cost
4	34787	8	8.2	8.2	6.2	3	11.84
5	34787	8	8.2	8.2	6.2	3	11.84
6	79124	5	20	20	12	27	NA
7							NA
8							NA
9							NA
10	34787	8	8.2	8.2	6.2	3	11.84
Sheet1

Cell Formulas
Range	Formula
G4:G10	G4	=IFERROR(INDEX(Sheet2!$I$2:$I$11,MATCH(B4&"|"&F4,INDEX(Sheet2!$E$2:$E$11&"|"&Sheet2!$D$2:$D$11,0),0)),"NA")

 

[baggers719]

Thank you! It almost worked, and maybe it is me trying to translate the formula you gave into my spreadsheet. I get an error for when zone is 5 and weight is 27 though. It should be $31.32. I put some screenshots of my actual tabs below to maybe help further.

 

[Fluff]

You need to swap the B6&"|"F6 round.
From the data you originally showed along with the example you gave I assumed that the Zone & Weights were in E & D not D & E