I am trying to do a do a sumif or vlookup formula but I am stuck. I am
trying to insert a formula into Sheet 1 (see below where i have =formula?).
I'm referencing the data on Sheet 2 below. Trying to look up by State code,
Locality, CPT, and Mod and then return the charge from Sheet 2 below. Any
ideas?

SHEET 1
Col A Col B Col C Col D
CPT CODE MOD DESCRIPTION STATE
510
LOCALITY
0

70010 26 CT HEAD/BRAIN W/O DYE =formula ?



SHEET 2
STATE LOCALITY CPT CODE MOD CHARGE
510 0 70010 26 $54.12
511 1 70010 26 $58.09
511 99 70010 26 $55.06
512 0 70010 26 $54.34
520 13 70010 26 $53.61
510 0 70015 26 $55.14
511 1 70015 26 $59.51
511 99 70015 26 $56.41
512 0 70015 26 $55.53
520 13 70015 26 $54.43

It is difficult to see the data from what is posted, but play around with
this

=INDEX(Sheet2!E1:E100,MATCH(1,(Sheet2!A1:A100=A2)* (Sheet2!B1:B100=B2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Katie" wrote in message
...
I am trying to do a do a sumif or vlookup formula but I am stuck. I am
trying to insert a formula into Sheet 1 (see below where i have
=formula?).
I'm referencing the data on Sheet 2 below. Trying to look up by State
code,
Locality, CPT, and Mod and then return the charge from Sheet 2 below. Any
ideas?

SHEET 1
Col A Col B Col C Col D
CPT CODE MOD DESCRIPTION STATE
510
LOCALITY
0

70010 26 CT HEAD/BRAIN W/O DYE =formula ?



SHEET 2
STATE LOCALITY CPT CODE MOD CHARGE
510 0 70010 26 $54.12
511 1 70010 26 $58.09
511 99 70010 26 $55.06
512 0 70010 26 $54.34
520 13 70010 26 $53.61
510 0 70015 26 $55.14
511 1 70015 26 $59.51
511 99 70015 26 $56.41
512 0 70015 26 $55.53
520 13 70015 26 $54.43

I find in cases like this that it is easier to introduce a new column
in your Sheet2 between MOD and CHARGE, and to put this formula in E2:

=A2&B2&C2&D2

and copy this down as far as required.

Then your formula in Sheet1 would be:

=VLOOKUP(C2&D2&A2&B2,Sheet2!E:F,2,0)

(well, I think this is right, but your alignment is not very good in
the example).

Hope this helps.

Pete

On Jul 2, 4:08*pm, Katie wrote:
I am trying to do a do a sumif or vlookup formula but I am stuck. *I am
trying to insert a formula into Sheet 1 (see below where i have =formula?). *
I'm referencing the data on Sheet 2 below. *Trying to look up by State code,
Locality, CPT, and Mod and then return the charge from Sheet 2 below. *Any
ideas? *

SHEET 1
Col A * * * * * Col B * * *Col C * * * * * * * * * * * * * * * * * * Col D
CPT CODE * * * * * MOD *DESCRIPTION * * * * * * * * STATE
* * * * * * * * * * * * * * * * * * * * * * * * * * 510
* * * * * * * * * * * * * * * * * * * * * * * * * * LOCALITY
* * * * * * * * * * * * * * * * * * * * * * * * * * 0

70010 * * * 26 *CT HEAD/BRAIN W/O DYE * =formula ?

SHEET 2
STATE * LOCALITY * * * *CPT CODE * * * *MOD * * CHARGE
510 * * 0 * * * 70010 * 26 * * *$54.12
511 * * 1 * * * 70010 * 26 * * *$58.09
511 * * 99 * * *70010 * 26 * * *$55.06
512 * * 0 * * * 70010 * 26 * * *$54.34
520 * * 13 * * *70010 * 26 * * *$53.61
510 * * 0 * * * 70015 * 26 * * *$55.14
511 * * 1 * * * 70015 * 26 * * *$59.51
511 * * 99 * * *70015 * 26 * * *$56.41
512 * * 0 * * * 70015 * 26 * * *$55.53
520 * * 13 * * *70015 * 26 * * *$54.43