What to do when vlookup() or index(match()) take up too many resources?

[acemanhattan]

I have a model that takes raw healthcare data and then reprices it based on the procedure type, and the corresponding relevant values from a fee schedule. Basically on a given row we'll look at the procedure code, go find that on our fee schedule, and then return values corresponding information from the fee schedule (fee, type, and a few other pieces of info helpful to pricing).

The problem I'm having is that the raw data set can be up to 13,000 rows, with each row looking at the fee schedule up to 6 times, with the fee schedule itself being 15,000 rows; basically I think the number of Index(Matches)) I'm using, and the range over which I'm using it has become prohibitive (model runs really really slow). I need to figure out a way to optimize this if possible. Data is like this

Raw data would be the "Code" (and other stuff) with additional columns being index(match()) formulas; codes can show up an unlimited number of times and we can have tens of thousands of rows.

Code	Flag1	Flag2	Flag3
a	Y	25.00	1.2
b	Y	12	13
a	Y	25	1.2

<tbody>
</tbody>

Fee Schedule would have no calculations (code only shows up once extend 15,000 rows).

Code	Flag1	Flag2	Flag3
a	Y	25.00	1.2
b	Y	12	13
c	N	1.5	.01
d	Y	NSP	0

<tbody>
</tbody>

Any thoughts?

 

[sheetspread]

powerpivot