Help with IF, Nested IF and Vlookup! I dont know where to start

[mookiebar]

i have a certain reimbursement rate for certain zip codes. There are 2 levels, rural and super rural. Each has its own rate of reimbursement. For instance, Rural is 284.75, while super rural is 349.10-for one particular service (A0426)


Part of the problem is that there are different services. (A0426, A0427, A0428, A0429, A0433 and A0434). Each pays a different rate.
There is also mileage which is paid per mile (A0425).


We want to type in a zip code, service and mileage and have a formula that figures out how much to bill. Where do i start??? thanks

 

[Caribeiro77]

Welcome,
In my opinion you should first of all create a table with the following headers:
Zip Code;Rural/Super Rural;Service;Rate

After that it will be very easy to come to the results you need.

i have a certain reimbursement rate for certain zip codes. There are 2 levels, rural and super rural. Each has its own rate of reimbursement. For instance, Rural is 284.75, while super rural is 349.10-for one particular service (A0426)


Part of the problem is that there are different services. (A0426, A0427, A0428, A0429, A0433 and A0434). Each pays a different rate.
There is also mileage which is paid per mile (A0425).


We want to type in a zip code, service and mileage and have a formula that figures out how much to bill. Where do i start??? thanks

 

[mookiebar]

I wish it were that simple.
Can you explain why i would need columns for service and rate?
In my mind i though i could just type in a zip code, service and # of miles and I would get a total billed amount. Its much more complex than i anticipated. Thanks!

 

[Caribeiro77]

If i understood correctly what you need, i don't know if something similar to this example bellow would help.
You have the table from A1:E17 and then the criterias and result from G1:H5

Book1
	A	B	C	D	E	F	G	H
1	Zip Code	Rural/Super Rural	Service	Rate	Cost		Zip Code	45678
2	12345	Rural	A0426	284,75	100		Rural/Super Rural	Super Rural
3	12345	Super Rural	A0426	349,1	200		Service	A0427
4	45678	Rural	A0427	222	300		Rate	111
5	45678	Super Rural	A0427	111	100		Cost	11100
6	56446	Rural	A0428	333	200
7	56446	Super Rural	A0428	115	300
8	65465	Rural	A0429	222	150
9	65465	Super Rural	A0429	111	100
10	54564	Rural	A0433	333	200
11	54564	Super Rural	A0433	115	300
12	11111	Rural	A0434	222	100
13	11111	Super Rural	A0434	111	200
14	33333	Rural	A0426	333	300
15	33333	Super Rural	A0426	115	150
16	55555	Rural	A0427	222	300
17	55555	Super Rural	A0427	111	150
Sheet3

Cell Formulas
Range	Formula
H3	{=INDEX($C$2:$C$17,MATCH(H$1&H$2,$A$2:$A$17&$B$2:$B$17,0))}
H4	{=INDEX($D$2:$D$17,MATCH(H$1&H$2,$A$2:$A$17&$B$2:$B$17,0))}
H5	{=INDEX($D$2:$D$17,MATCH(H$1&H$2,$A$2:$A$17&$B$2:$B$17,0))*INDEX($E$2:$E$17,MATCH(H$1&H$2,$A$2:$A$17&$B$2:$B$17,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Caribeiro77]

https://www.dropbox.com/s/bala41z5li1u9ip/Example File.xlsx?dl=0

 

[mookiebar]

Got it! many thanks