Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 130
Default sumif or vlookup help

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default sumif or vlookup help

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default sumif or vlookup help

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP & SUMIF Jock Excel Worksheet Functions 7 April 2nd 23 07:53 PM
HOW TO COUPLE VLOOKUP & SUMIF SSJ New Users to Excel 5 February 27th 08 04:43 PM
VLOOKUP, SUMPRODUCT, or SUMIF? steph Excel Worksheet Functions 5 March 28th 07 04:26 PM
match mix with sumif and vlookup Manos Excel Worksheet Functions 3 February 28th 05 12:29 PM
SUMIF using VLOOKUP as criteria Telly Excel Worksheet Functions 1 February 18th 05 11:17 PM


All times are GMT +1. The time now is 11:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"