If and vlookup formula

[jabersold]

I need help on constructing a look up. In my spread sheet I will plug in the service type, zone and weight in three different cells. I need it to go to a table that called rates and find the rate based on the info found in the table. example of the table below.


[TABLE="width: 718"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Service[/TD]
[TD]Lbs.[/TD]
[TD]Zone 2[/TD]
[TD]Zone 3[/TD]
[TD]Zone 4[/TD]
[TD]Zone 5[/TD]
[TD]Zone 6[/TD]
[TD]Zone 7[/TD]
[TD]Zone 8[/TD]
[/TR]
[TR]
[TD]UPS GND RESI[/TD]
[TD]1.00[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[/TR]
[TR]
[TD]UPS GND RESI[/TD]
[TD]2.00[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.83[/TD]
[TD]10.99[/TD]
[/TR]
[TR]
[TD]UPS GND RESI[/TD]
[TD]3.00[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.76[/TD]
[TD]11.09[/TD]
[TD]11.38[/TD]
[TD]11.81[/TD]
[/TR]
[TR]
[TD]UPS GND RESI[/TD]
[TD]4.00[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]10.90[/TD]
[TD]11.27[/TD]
[TD]11.52[/TD]
[TD]12.04[/TD]
[TD]12.56[/TD]
[/TR]
[TR]
[TD]UPS GND RESI[/TD]
[TD]5.00[/TD]
[TD]10.63[/TD]
[TD]10.63[/TD]
[TD]11.14[/TD]
[TD]11.62[/TD]
[TD]11.92[/TD]
[TD]12.41[/TD]
[TD]13.07[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

Need example of 3 input values and what expected output rate should be

From the example, it seems like you only need the weight and zone (2 variables) to return a rate

Also missing, sheet name, address of table, header row, address of input variables, things that help someone who can't see your PC screen or sheet, that might be useful!

 

[jabersold]

Thank you for the reply.

The sheet name is "Rates" In the example I posted did not show it but there are several different service types. The table has probably 250 lines of rates under 7 different service types. So I want to plug in a service type in "A1" and a weight in "B1" and have the zone in "C1" already and it go to the table in the sheet labeled and Rates and bring back the rate that corresponds to that info.

 

[jtakw]

Hi,

May be this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Service Type	Weight	Zone	Result		Service	Lbs.	Zone 2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7	Zone 8
2	UPS GND RESI	3	Zone 5	10.76		UPS GND RESI	1	10.63	10.63	10.63	10.63	10.63	10.63	10.63
3	UPS AIR RESI	6	Zone 7	16		UPS GND RESI	2	10.63	10.63	10.63	10.63	10.63	10.83	10.99
4						UPS GND RESI	3	10.63	10.63	10.63	10.76	11.09	11.38	11.81
5						UPS GND RESI	4	10.63	10.63	10.9	11.27	11.52	12.04	12.56
6						UPS GND RESI	5	10.63	10.63	11.14	11.62	11.92	12.41	13.07
7						UPS AIR RESI	6	11	12	13	14	15	16	17
8						UPS GND RESI	7	11	11.5	12	12.5	13	13.5	14
Sheet252

Cell Formulas
Range	Formula
D2	=SUMPRODUCT((F$2:F$8=A2)*(G$2:G$8=B2)*(H$1:N$1=C2)*H$2:N$8)

Change/adjust cell references/ranges and add sheet name according to your data.

 

[jabersold]

Thank you very much for the assist

 

[jtakw]

You're welcome.