tiered lookup

[ajm]

folks, i am trying to match and compare two data sets to ensure we are charging the correct amount. One contains the rates charged for medical procedures (Rates) and the associated service fee charged for ancillary items, and the other contains patient level data (Patients) including fees and charges ;levied for their procedure. the common item is the procedure code. On the Rates sheet, the procedure codes can be grouped into bands (eg 12345 - 23456) or can be singular (eg 34567). To make things more interesting, there are a handful of procedure codes that are split into tiers according to the cost of the associated ancillary items.

For example, Patient A has had Procedure 34567 performed. It is a single rate procedure so a simple look up of that Procedure code on the Rate sheet is all that is required. Patient B has a more complex procedure performed with tiered ancillary costs. Procedure 12444, which cost $180. This attracts ancillary charges of 43%. Had it been the same procedure with less time involved, it would have been $140. This attracts ancillary charges of 40%. so, How do i do a lookup to take both the procedure bands on the Rate sheet and the tiered level of charge so that I get the correct level of ancillary per centage.

This is the data (the tiered procedure codes are in yellow):

Book1
	A	B	C	D	E	F	G	H
1						FORMULA	FORMULA	FORMULA
2	Patient Name	MBS Item Code	Item Receipt Amount	Item Service Fee Amount	Item Service Fee GST Amount	Eff Service Chg %	Register Rate	VARIANCE
3	Patient 1	300	64.2	16.05	1.605	25%	25%	0%
4	Patient 2	20000	185.9	83.655	8.3655	45%	60%	-15%
5	Patient 3	11910	64.2	16.05	1.605	25%	30%	-5%
6	Patient 4	11915	26.6	13.3	1.33	50%	30%	20%
7	Patient 5	11925	181.15	81.5175	8.15175	45%	30%	15%
8	Patient 6	400	75.5	18.875	1.8875	25%	25%	0%
9	Patient 7	10000	31.25	15.625	1.5625	50%	25%	25%
Patients

Cell Formulas
Range	Formula
F3	=D3/C3
F4	=D4/C4
F5	=D5/C5
F6	=D6/C6
F7	=D7/C7
F8	=D8/C8
F9	=D9/C9
G3	=VLOOKUP(B3,RATES!ServiceFees,2,1)
G4	=VLOOKUP(B4,RATES!ServiceFees,2,1)
G5	=VLOOKUP(B5,RATES!ServiceFees,2,1)
G6	=VLOOKUP(B6,RATES!ServiceFees,2,1)
G7	=VLOOKUP(B7,RATES!ServiceFees,2,1)
G8	=VLOOKUP(B8,RATES!ServiceFees,2,1)
G9	=VLOOKUP(B9,RATES!ServiceFees,2,1)
H3	=F3-G3
H4	=F4-G4
H5	=F5-G5
H6	=F6-G6
H7	=F7-G7
H8	=F8-G8
H9	=F9-G9

Named Ranges
Name	Refers To	Cells
ServiceFees	=RATES!$F$4:$G$15
RATES!ServiceFees	=RATES!$F$4:$G$15

Book1
	A	B	C	D	E	F	G
3	Professional Attendances				Professional Attendances
4	Procedure 1	1 - 10799	25%		Description 1	1	25%
5	Procedure 2	10800 - 10949	30%		Description 2	10800	30%
6	Diagnostic Procedures and Investigations				Diagnostic Procedures and Investigations
7	Procedure 3	11000 - 11699	48%		Description 3	11000	48%
8	Procedure 4	11700 - 11799	50%		Description 4	11700	50%
9	Procedure 5	11800 - 11899	43%		Description 5	11800	43%
10	Procedures 6 (< $150)	11900 - 11999	48%		Description 6	11900	48%
11	Procedures 6 ($150 to $500)		38%		Description 7	11900	38%
12	Procedures 6 ($501 to $1000)		60%		Description 8	11900	60%
13	Procedures 6 (> $1001)		30%		Description 9	11900	30%
14	Procedure 10	12202 - 12299	60%		Description 10	12202	60%
15	Procedure 11	12300 - 12599	60%		Description 11	12300	60%
RATES

Cell Formulas
Range	Formula
F3	=IF(B3="","",IF(ISERROR(FIND(" - ",B3)),B3*1,LEFT(B3,FIND(" - ",B3))*1))
F4	=IF(AND(B4="",C4=""),"",IF(AND(B4="",C4<>""),F3,IF(ISERROR(FIND(" - ",B4)),B4*1,LEFT(B4,FIND(" - ",B4))*1)))
F5	=IF(AND(B5="",C5=""),"",IF(AND(B5="",C5<>""),F4,IF(ISERROR(FIND(" - ",B5)),B5*1,LEFT(B5,FIND(" - ",B5))*1)))
F6	=IF(AND(B6="",C6=""),"",IF(AND(B6="",C6<>""),F5,IF(ISERROR(FIND(" - ",B6)),B6*1,LEFT(B6,FIND(" - ",B6))*1)))
F7	=IF(AND(B7="",C7=""),"",IF(AND(B7="",C7<>""),F6,IF(ISERROR(FIND(" - ",B7)),B7*1,LEFT(B7,FIND(" - ",B7))*1)))
F8	=IF(AND(B8="",C8=""),"",IF(AND(B8="",C8<>""),F7,IF(ISERROR(FIND(" - ",B8)),B8*1,LEFT(B8,FIND(" - ",B8))*1)))
F9	=IF(AND(B9="",C9=""),"",IF(AND(B9="",C9<>""),F8,IF(ISERROR(FIND(" - ",B9)),B9*1,LEFT(B9,FIND(" - ",B9))*1)))
F10	=IF(AND(B10="",C10=""),"",IF(AND(B10="",C10<>""),F9,IF(ISERROR(FIND(" - ",B10)),B10*1,LEFT(B10,FIND(" - ",B10))*1)))
F11	=IF(AND(B11="",C11=""),"",IF(AND(B11="",C11<>""),F10,IF(ISERROR(FIND(" - ",B11)),B11*1,LEFT(B11,FIND(" - ",B11))*1)))
F12	=IF(AND(B12="",C12=""),"",IF(AND(B12="",C12<>""),F11,IF(ISERROR(FIND(" - ",B12)),B12*1,LEFT(B12,FIND(" - ",B12))*1)))
F13	=IF(AND(B13="",C13=""),"",IF(AND(B13="",C13<>""),F12,IF(ISERROR(FIND(" - ",B13)),B13*1,LEFT(B13,FIND(" - ",B13))*1)))
F14	=IF(AND(B14="",C14=""),"",IF(AND(B14="",C14<>""),F13,IF(ISERROR(FIND(" - ",B14)),B14*1,LEFT(B14,FIND(" - ",B14))*1)))
F15	=IF(AND(B15="",C15=""),"",IF(AND(B15="",C15<>""),F14,IF(ISERROR(FIND(" - ",B15)),B15*1,LEFT(B15,FIND(" - ",B15))*1)))
G4	=IF(F4="","",C4)
G5	=IF(F5="","",C5)
G6	=IF(F6="","",C6)
G7	=IF(F7="","",C7)
G8	=IF(F8="","",C8)
G9	=IF(F9="","",C9)
G10	=IF(F10="","",C10)
G11	=IF(F11="","",C11)
G12	=IF(F12="","",C12)
G13	=IF(F13="","",C13)
G14	=IF(F14="","",C14)
G15	=IF(F15="","",C15)

 

[ajm]

I worked this out this morning. There may be a more elegant solution out there.

to begin with, I copied the specific ranges with their tiered rates (there are two in my live data) to a third sheet called Rules. These are split out with the lower and upper range in individual cells. I also added a table for the tiered rates with the lower limit of each tier in the first row and the number of associated offset rows i need in the second row. I called this Range: DollarValueOffset

=IF(B3="NE",Rules!$H$4,IF(NOT(OR(AND(B3>=Rules!$D$8,B3<=Rules!$E$8),AND(B3>=Rules!$D$9,B3<=Rules!$E$9))),VLOOKUP(B3,ServiceFees,2,1),OFFSET(INDEX(Rates!$G$4:$G$101,MATCH(B3,Rates!$F$4:$F$101,1),1),HLOOKUP(C3,DollarValueOffset,2,1),0)))

so, it reads,

IF(B3="NE",Rules!$H$4

if the procedure is an "NE", use the value held in Rules H4. There is only ever a handful of these and they are straightforward so get them out of the way first.

IF(NOT(OR(AND(B3>=Rules!$D$8,B3<=Rules!$E$8),AND(B3>=Rules!$D$9,B3<=Rules!$E$9))),VLOOKUP(B3,ServiceFees,2,1)

**Service Fees is the two columns F & G on Rates tab

next, if not NE, then find those that are not within the tiered ranges. these need just a simple vlookup to clear them up. so, I have identified those procedure codes that are within the two ranges with the two And statements. The lookup works on those codes that are NOT within the specified ranges.

now the fun part:

OFFSET(INDEX(Rates!$G$4:$G$101,MATCH(B3,Rates!$F$4:$F$101,1),1),HLOOKUP(C3,DollarValueOffset,2,1),0)))

as you cannot use offset with vlookup (vlookup returns a value, offset is a range function), I used Index/Match for the lookup. this finds the first instance of the Procedures with the tiered rates. It references the DollarValueOffset named range in an Hlookup which holds the tiered dollar values and the number of rows to offset from the lowest tiered value. (0 rows for values up to $150, 1 row for values above $150 and below $500, etc).

I hope this makes enough sense for anyone in a similar position to make use of.

happy days.