Value return from two column comparison

**mwsc** · 10-22-2012, 03:51 PM

Thanks to anyone who can help me!

I work for a medical practice, and I'm trying to quickly find the payments of our contractual reimbursements based on the insurance name and CPT (procedure code). Hopefully I'll explain this clearly...I have a spreadsheet where I'm trying to get a return value if two columns of criteria are selected/compared.

On the first worksheet I have a mixture of all of our procedures billed for the year (over 18,000 rows). IE -

Aetna	99214
Aetna	31231
Aetna	92511

On the second worksheet I have what we should be paid from those insurance companies for each procedure. IE -

Aetna	99214	$175
Aetna	31231	$350
Aetna	92511	$300

What formula can I use to quickly lookup the reimbursement we should have received?

Thanks again for your help!

**Kevin UK** · 10-22-2012, 03:55 PM

How about the VLOOKUP or INDEX and MATCH

**mwsc** · 10-22-2012, 04:01 PM

Well, I think the problem is that I'm an idiot. Every formula I've input fails, and I'm not sure what I'm doing wrong.

**Kevin UK** · 10-22-2012, 04:08 PM

Have a look at these links, if you still have a problem, post a spread sheet with some dummy data.

VLOOKUP

INDEX and MATCH

**FDibbins** · 10-22-2012, 04:09 PM

if you are trying to look INTO your 2nd table, and return a value to your 1st table, use this...
=VLOOKUP(B1,$B$6:$C$8,2,FALSE)
this assumes that the procedure code will always be unique

**mwsc** · 10-22-2012, 04:39 PM

I appreciate the input so far, but I'm still not getting valid results.

I've uploaded a dummy document. I'd like to pull the "Contractual Payment" data from the "Contractual" Tab for the corresponding data in the "CPT" Tab.

Thanks for the help!

**FDibbins** · 10-22-2012, 05:07 PM

thats because the "numbers" in sheet1 are actually text that just looks like a number. highlight that entire range of "numbers", scroll back up to the top and you will see little yellow "warning" label. click on that and select 'convert to number", then try again

**Kevin UK** · 10-23-2012, 10:10 AM

Hi mwsc

As FDibbins points out, the numbers in the CPT column on the CPT sheet are text, so you need to convert those numbers.
See attached sheet, VLOOKUP in column E + INDEX and MATCH in column F.