Lookups and Cross Referencing - QUESTION PLEEAAASE HELP

[brandonhauk]

Hello all, I need your expertise on this one. I have 2 worksheets, 1 which is a master list of referrals data (.i.e. pt name, dob, etc.) and 1 which is a list of specialists within a county. I need a formula that will pull/lookup data in the master list of referrals with a specific name (in this case the name of the specialist), and return the data to a specific cell next to the same specific name in the specialist worksheet. There may be more than one value that pulls to the cell, but thats what I want. In non excel verbiage, i want to be able to pull all the insurances for a specific specialist (by name) in the master list, and populate a cell to show all of the insurance for that specialist with the correlating name in the specialty sheet.Hope this makes sense. Please please help, my brain hurts aahhhhhhh

 

[shadow12345]

Re: Lookups and Cross Referencing - QUESTION PLEEAAASE HELP - IMAGE

You do this...

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "right" valign = "bottom" bgcolor ="#FAFAFA"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "right" valign = "bottom" bgcolor ="#FAFAFA"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "right" valign = "bottom" bgcolor ="#FAFAFA"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#000000"><b><u>Spec Name </u></b></font></td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#333333"><b><u>Specialty </u></b></font></td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333"><b><u>Data Pull </u></b></font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">CRMC- Oral Maxillofacial Clinic </font></td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Oral and Maxo Facial Surg </font></td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A3,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Doris Stein Eye Research Center </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A4,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Nguyen, Houng </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Endocrinology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A5,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Natural Vision - Madera </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A6,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Kaye, David MD </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A7,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>8</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Kaweah Delta (Exeter Health Clinic </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Cardiology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A8,Sheet1!$A:$D,3,FALSE) </font></td></tr>
</table>


N/A means the Spec Name in Sheet2 can't be found in Ref Source in sheet 1

 

[brandonhauk]

Re: Lookups and Cross Referencing - QUESTION PLEEAAASE HELP - IMAGE

I tried this but here is the problem, I need to formula to recognize the matching specialist name in sheet one with the correlating insurance and populate C3 with the insurances found. This is only evaluating the 3rd column in the original sheet. Does that make sense? this is why I'm stuggling LOL

 

[shadow12345]

Re: Lookups and Cross Referencing - QUESTION PLEEAAASE HELP - IMAGE

No... im confused....

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="342" height="30" align = "right" valign = "bottom" bgcolor ="#FAFAFA"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "right" valign = "bottom" bgcolor ="#FAFAFA"><font color="#000000"> </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "right" valign = "bottom" bgcolor ="#FAFAFA"><font color="#000000"> </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="342" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#000000"><b><u>Spec Name </u></b></font></td><td rowspan="1" colspan="1" width="342" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#333333"><b><u>Specialty </u></b></font></td><td rowspan="1" colspan="1" width="342" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333"><b><u>Data Pull </u></b></font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="342" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">CRMC- Oral Maxillofacial Clinic </font></td><td rowspan="1" colspan="1" width="342" height="102" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Oral and Maxo Facial Surg </font></td><td rowspan="1" colspan="1" width="342" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">~Sante Community Physicians-Fresno </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Doris Stein Eye Research Center </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">~Sante Community Physicians-Fresno </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Nguyen, Houng </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Endocrinology </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">#N/A </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Natural Vision - Madera </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Advantek Benefit Administrator </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Kaye, David MD </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">#N/A </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>8</b></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Kaweah Delta (Exeter Health Clinic </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Cardiology </font></td><td rowspan="1" colspan="1" width="342" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">#N/A </font></td></tr>
</table>


Maybe you could manually fill out the sheet 2 so I can see what data you aren't getting?

Its looking at Spec Name in sheet2, finding that in sheet1 .... then putting the insurance details into sheet2.

 

[brandonhauk]

Re: Lookups and Cross Referencing - QUESTION PLEEAAASE HELP - IMAGE

Hi there I'm sorry, sheet 1 and sheet 2 data were listed on the HTML pic I sent look at the labels below the image in my first reply, sorry didnt know you didnt see that

 

[shadow12345]

Re: Lookups and Cross Referencing - QUESTION PLEEAAASE HELP - IMAGE

Yes I have that split across both.... this is what I have in sheet 1....

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td><td align="center" bgcolor="#C0C0C0"><b>D</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000"><b>REF SOURCE </b></font></td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FFCC99"><font color="#000000"><b>SPECIALTY </b></font></td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000"><b>INSUR </b></font></td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FFCC99"><font color="#000000"><b>REF FOR </b></font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#333333">CRMC- Oral Maxillofacial Clinic </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Oral and Maxo Facial Surg </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">~Sante Community Physicians-Fresno </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">GI </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#333333">Doris Stein Eye Research Center </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">~Sante Community Physicians-Fresno </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">Physical Therapy </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#333333">Natural Vision - Madera </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">Advantek Benefit Administrator </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">Radiology </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">Unknown </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">Radiology </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">Advantek Benefit Administrator </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">(Not Specified) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">Physcians Imaging </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">Radiology </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">Advantek Benefit Administrator </font></td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">Radiology </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>8</b></td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">Gastroenterology and Hepatology, Farooqi, Saadat MD </font></td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">GI </font></td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#000000">Aetna Choice POS II </font></td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FFFFFF"><font color="#000000">GI </font></td></tr>
</table>

Then On sheet 2 I have....

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#000000"><b><u>Spec Name </u></b></font></td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#FF99CC"><font color="#333333"><b><u>Specialty </u></b></font></td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333"><b><u>Data Pull </u></b></font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">CRMC- Oral Maxillofacial Clinic </font></td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Oral and Maxo Facial Surg </font></td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A3,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Doris Stein Eye Research Center </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A4,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Nguyen, Houng </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Endocrinology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A5,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Natural Vision - Madera </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A6,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Kaye, David MD </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Optometry/Opthomology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A7,Sheet1!$A:$D,3,FALSE) </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>8</b></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">Kaweah Delta (Exeter Health Clinic </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA"><font color="#333333">Cardiology </font></td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC"><font color="#333333">=VLOOKUP($A8,Sheet1!$A:$D,3,FALSE) </font></td></tr>
</table>


Its working on mine... as you can see in my post above. At least I think its giving the results it should have. Find the Spec Name in Col A of Sheet2 in Sheet 1 .... and put the data from Sheet 1 col C into column C in sheet 2.
Im not sure what its not doing for you.

 

[brandonhauk]

Re: Lookups and Cross Referencing - QUESTION PLEEAAASE HELP - IMAGE

Sorry for all of the confusion we are good to go. Thanks so much, I just had more data than what you were seeing in the HTML i sent so it was throwing off the formula. You're the best!

 

[shadow12345]

Re: Lookups and Cross Referencing - QUESTION PLEEAAASE HELP - IMAGE

most welcome.