sumif or vlookup help

[Katie]

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

 

[Bob Phillips]

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)

 

[Pete_UK]

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