Match Value in Cell to an Existing Column and Return Adjacent Cell Text

Question

I have a spreadsheet containing patients in column a, patient's diagnosis in column b, and their doctor in column c. I have another sheet that has the doctors listed in column a and their practice group in column b. I need a function that will look at each value in column c on sheet 1, match it to the doctor in column a on sheet 2 (Doctors List), and return the practice group to column d on sheet 1. I have tried a few formulas including this one

=IFERROR(VLOOKUP(C2,'Doctors List'!A:B,2,FALSE),"")

but can't seem to get anything to work! It just returns blanks. Please help!

**SHEET 1

Patient Name   Diagnosis    Attending Physician    Practice Group**
Patient A      Diagnosis    Dr. Smith   
Patient B      Diagnosis    Dr. John    
Patient C      Diagnosis    Dr. Joe 
Patient D      Diagnosis    Dr. Ken 
Patient E      Diagnosis    Dr. Williams    
Patient F      Diagnosis    Dr. Williams    
Patient G      Diagnosis    Dr. Smith   
Patient H      Diagnosis    Dr. Jones

**SHEET 2

Physician          Practice Group**
Dr. Smith          Practice A
Dr. John           Medical Group A
Dr. Joe            Practice B
Dr. Ken            Medical Group B
Dr. Williams       Practice C
Dr. Jones          Medical Group C

Answer 1

Try using MATCH and INDEX rather than VLOOKUP

So in D2 of sheet 1:

=INDEX(Sheet2!$B:$B,MATCH($C2,Sheet2!$A:$A,0))

and copy that formula down.

If you're looking to troubleshoot, your existing formula, try using "Evaluate" on the Formulas tab of Excel 2010 which can step you through the calculation.

I've had problems with text fields that have extra spaces after them, so I regularly use the "TRIM" function when doing lookups, or matches.