IF/AND or VLOOKUP?

[mjpish]

I have a spreadsheet of physician names, clinic referred to and number referred in columns. I need to transfer that data to another sheet. So I need to search for specific physician at a specific clinic (they work in multiple clinics) and pull the value associated with that to another sheet. The other sheet has different formatting so I can't simply link or cut/paste. I was fiddling with IF/AND or VLOOKUP, but can't seem to figure how to get a VLOOKUP to just pull the visits if the doctor name matches and the clinic name matches. Any help would be appreciated.

Below is a small sampel of the spreadsheet, each doctor name would be repeated and associated with different clinics:

Physician</SPAN>	Visits</SPAN>	Clinic</SPAN>
ABOKA, ALEXANDER M [1421635]</SPAN>	11</SPAN>	DMC PT AMELIA IM</SPAN>
ALADE, FOLASHADE O [8310]</SPAN>	1</SPAN>	DMC PT AMELIA IM</SPAN>
ALBAND, WENDY F [8655]</SPAN>	1</SPAN>	DMC PT AMELIA IM</SPAN>
ANDERSON, ROBIN N [1415117]</SPAN>	1</SPAN>	DMC PT AMELIA IM</SPAN>
ANTOINE, ANGELA [1415120]</SPAN>	1</SPAN>	DMC PT AMELIA IM</SPAN>

<TBODY>
</TBODY>

 

[Comfy]

There are several ways one if which is:

Excel 2010
	A	B	C
1	Physician	Visits	Clinic
2	ABOKA, ALEXANDER M [1421635]	11	DMC PT AMELIA IM
3	ALADE, FOLASHADE O [8310]	1	DMC PT AMELIA IM
4	ALBAND, WENDY F [8655]	1	DMC PT AMELIA IM
5	ANDERSON, ROBIN N [1415117]	1	DMC PT AMELIA IM
6	ANTOINE, ANGELA [1415120]	1	DMC PT AMELIA IM
7
8
9
10
11	ALBAND, WENDY F [8655]	DMC PT AMELIA IM	1
Sheet1

Cell Formulas
Range	Formula
C11	{=INDEX($B$2:$B$6,MATCH(A11&B11,A2:A6&C2:C6,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[FormR]

Seeing as the lookup value is numeric, this is another option:

Excel Workbook
	A	B	C
1	Physician	Visits	Clinic
2	ABOKA, ALEXANDER M [1421635]	11	DMC PT AMELIA IM
3	ALADE, FOLASHADE O [8310]	1	DMC PT AMELIA IM
4	ALBAND, WENDY F [8655]	1	DMC PT AMELIA IM
5	ANDERSON, ROBIN N [1415117]	1	DMC PT AMELIA IM
6	ANTOINE, ANGELA [1415120]	1	DMC PT AMELIA IM
7
8
9	ALBAND, WENDY F [8655]	DMC PT AMELIA IM	1
Sheet1

 

[mjpish]

Seeing as the lookup value is numeric, this is another option:

Sheet1

*	A	B	C
1	Physician	Visits	Clinic
2	ABOKA, ALEXANDER M [1421635]	11	DMC PT AMELIA IM
3	ALADE, FOLASHADE O [8310]	1	DMC PT AMELIA IM
4	ALBAND, WENDY F [8655]	1	DMC PT AMELIA IM
5	ANDERSON, ROBIN N [1415117]	1	DMC PT AMELIA IM
6	ANTOINE, ANGELA [1415120]	1	DMC PT AMELIA IM
7	*	*	*
8	*	*	*
9	ALBAND, WENDY F [8655]	DMC PT AMELIA IM	1

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 257px"><COL style="WIDTH: 159px"><COL style="WIDTH: 159px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula C9 =SUMIFS($B$1:$B$6,$A$1:$A$6,A9,$C$1:$C$6,B9) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks so much to both comments above!

I am having some trouble with that formula calling to another sheet within the same excel workbook:

=SUMIFS('Mel template'!C1:C1535,'Mel template'!B1:B1545,'Mel detail template'!A1058,'Mel template'!A1:A1535,'Mel detail template'!C6)

C colum is visits, B column is doctor, A column is clinic

It does not seem to recognize the 'Mel template! in the formula, it only puts in color and seems to recognize the links to the same sheet 'Mel detail template!

Does the results row need to be setup in order or can each cell be called to as formula above does with 'Mel detail template - for Dr name and Clinic name?

 

[FormR]

Hi, does this help?

If not - what cells on the sheet where you are putting the formula contain the Clinic and Doctor that you want to look up on the "Template" sheet?

Excel Workbook
	A	B	C
1	Clinic	Physician	Visits
2	DMC PT AMELIA IM	ALBAND, WENDY F [8655]	1
Other

Excel Workbook
	A	B	C
1	Clinic	Physician	Visits
2	DMC PT AMELIA IM	ABOKA, ALEXANDER M [1421635]	11
3	DMC PT AMELIA IM	ALADE, FOLASHADE O [8310]	1
4	DMC PT AMELIA IM	ALBAND, WENDY F [8655]	1
5	DMC PT AMELIA IM	ANDERSON, ROBIN N [1415117]	1
6	DMC PT AMELIA IM	ANTOINE, ANGELA [1415120]	1
Mel template

 

[mjpish]

Hi, does this help?

If not - what cells on the sheet where you are putting the formula contain the Clinic and Doctor that you want to look up on the "Template" sheet?

Other

*	A	B	C
1	Clinic	Physician	Visits
2	DMC PT AMELIA IM	ALBAND, WENDY F [8655]	1

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 175px"><COL style="WIDTH: 220px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula C2 =SUMIFS('Mel template'!C:C,'Mel template'!A:A,A2,'Mel template'!B:B,B2) <TBODY> </TBODY>

<TBODY>
</TBODY>

Mel template

*	A	B	C
1	Clinic	Physician	Visits
2	DMC PT AMELIA IM	ABOKA, ALEXANDER M [1421635]	11
3	DMC PT AMELIA IM	ALADE, FOLASHADE O [8310]	1
4	DMC PT AMELIA IM	ALBAND, WENDY F [8655]	1
5	DMC PT AMELIA IM	ANDERSON, ROBIN N [1415117]	1
6	DMC PT AMELIA IM	ANTOINE, ANGELA [1415120]	1

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 159px"><COL style="WIDTH: 242px"><COL style="WIDTH: 58px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Problem solved! I needed to matchup the ranges for each column exactly and it gave me what I needed! Thanks so much