New to excel: Calculator or estimator

New Contributor

I am trying to create from the Medicare fee schedule which has CPT codes and a payment amount.

BargieGirl_0-1641744885552.png

a cost estimator.  I can use the see the FS(fee schedule) that sheet is on or a new sheet.  I need to be able to type in the CPT code and the amount pull up(par amount).  Then I can do the formula of 20% of that amount and the patient will know what they are responsible for.  My problem is what function or how do I pull that information into a new cell.  Example: CPT 60100 has the amount of $110.98, how can I type the CPT code in one column and it pull up the amount in a second column? 

TYPE CPT CODE60100#NAME?

 Please advise,

4 Replies

Hi @BargieGirl 

 

the function that you can use for this is VLOOKUP.

 

For your example, if the CPT schedule is in Sheet2 and you type the CPT code in cell B1 on Sheet1, you can use this formula 

=VLOOKUP(B1;Sheet2!$B$2:$D$7;3;FALSE)

 

Just note that you have to adjust the lookup-range according to the real number of lines in your table. So instead of $B$2:$D$7 it could be $B$2:$D$100, if you have 100 lines.

 

@DTE    here is the formula I used.  =VLOOKUP(K5,$B$2:$D$11010,4,FALSE)  I tried to do it on the same sheet.  So basically, what I am trying to do  is to be able type a cpt code in one cell and then the cell next to it the pmt comes up from a formula or Vlookup.  I wish I could send you the spreadsheet  and you see if you can make something.

Still doesn't work.     

CPTPMT 
   
60500#N/A 
78800#N/A 
76536#N/A 
  TOTAL
  X20%

Hi @BargieGirl 

 

your formula has one error:

you refer to $B$2:$D$11010, which covers 3 columns (B, C, D). But in the next argument, you refer to column 4, which is not possible. Instead, it should be

=VLOOKUP(K5,$B$2:$D$11010,3,FALSE)

 

If you still do not get a proper result, the reason might be the data format: It looks as if the values in column B are text (what you can see on the little green triangle in the left top corner of each cell).

Just make sure, that the cell K5 is uses also text format for the entry. Otherwise the vlookup tries to find a number in a range of text. This will not match, even if the values look the same.

 

@DTE   Thank you!  I managed to make it work. I had to get rid of the green corners and also to make sure my cells were text.