=if help associating medical code # with monetary value

**mervin** · 05-21-2008, 08:31 PM

Howdy guys,

I haven't used excel much since my undergraduate days, so most of what I've figured out how to do with excel had to be recalled from my memories of my computer science courses.

In short, I'm doing a spread sheet for my hospital department and I'm trying to associate a medical code number to the monetary value it represents. For example, the very first number I have listed is 20600, and this code represents a charge of $109.50. I've already tallied several colums with this formula and it seems to be working ok, but the last colum I tried to associate this formula with failed to work. I think it's probably because it's too long. Here is the entire formula I've been using. I know it's not perfect, but it seems to do the tirck. Then again, if anyone knows a better way to do this, I'm all ears

=IF(J3=20600,"$109.50",IF(J3=20605,"$120.00",IF(J3=20610,"$146.50",IF(J3=82962,"$13.00",IF(J3=51702,"$226.00",IF(J3=51701,"$155.00",IF(J3=93000,"$73.00",IF(J3=93040,"$35.50",IF(J3=82270,"19.50"),IF(J3=86580,"$22.50",IF(J3=99024,"$0.00",IF(J3=11730,"$168.00",IF(J3=94060,"$140.00",IF(J3=29580,"$96.00",IF(J3=87210,"$25.50")))))

**mervin** · 05-21-2008, 09:03 PM

I'm uploading a file attachment to make it easier for people to understand what I'm trying to do. I'm sure there is a much more efficient way to do things than the way I've got this spreadsheet tabulated, but I'm not an excel expert. Maybe the VLOOKUP function would be a better fit for the type of spreadsheet I'm trying to create? Regardless, I hope someone can help me figure out how to get this to function the way I need it to.

**Trebor76** · 05-21-2008, 09:49 PM

Hi Mervin,

The original formula wasn't returning the last procedure amount ($25.50) for procedure code 87210 as you can only have a maximum of 7 nested IF statements in a formula - this one has 8.

Two alternatives are to either use a VLOOKUP (preferred if there's to be a large dataset on which to find the value for) or a LOOKUP formula - both of which I've written for you in cells K3 and K4 of Sheet1 of the attached.

HTH

Robert