Bring data from another workbook to current workbook if two criteria met

Hello,

I have a file "vlookup to" that has four columns:

PAYOR (populated)
DATE OF SERVICE (populated)
CPT CODE (populated)
RATE (need to get from "vlookup from" workbook)

I have a file "vlookup from" that has four sheets, each sheet has two columns in the data table:

CPT CODE (populated)
RATE (populated)

In cells B2 of each sheet is an "Effective" date
In cells B3 of each sheet is an "End" date

The data tables are named using the folowing methodology:
PAYOR&"Rates"&YYYY

What I want to do is look up both the PAYOR and the DATE OF SERVICE from the "vlookup to" file, find the appropriate sheet in the "vlookup from" file and then bring in the rate for the CPT CODE to the "vlookup to" "RATE" column.

For example, in row 2 of the "vlookup to" file, the row reads:
PAYOR Date of Service CPT CODE RATE
BX 2/16/2014 10021 needed

The macro should then go to the "vlookup from" file, find the "BXRates2014" table and vlookup the CPT CODE "10021" and return the rate of $2.00 to the "RATE" column in the "vlookup to" file.

Similarily, in row 18 of the "vlookup to" file, the macro should go to the "vlookup from" file, find the "HNRates2014" table and vlookup the CPT CODE "86485" and return the rate of $1.50 to the "RATE" column in the "vlookup to" file.

Any help or guidance would be greatly appreciated. Thank you in advance!

Hi Shawnvon,

Give this a try in D2:

Please Login or Register  to view this content.

Hope it works for you.