Lookups and Cross Referencing - QUESTION PLEEAAASE HELP

brandonhauk · Jun 4, 2014

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 · Jun 4, 2014

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

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

brandonhauk · Jun 4, 2014

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 · Jun 4, 2014

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

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 · Jun 4, 2014

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">A</td><td align="center" bgcolor="#C0C0C0">B</td><td align="center" bgcolor="#C0C0C0">C</td><td align="center" bgcolor="#C0C0C0">D</td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">2</td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FF99CC">REF SOURCE </td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FFCC99">SPECIALTY </td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FF99CC">INSUR </td><td rowspan="1" colspan="1" width="211.5" height="30" align = "left" valign = "center" bgcolor ="#FFCC99">REF FOR </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">3</td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">CRMC- Oral Maxillofacial Clinic </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FAFAFA">Oral and Maxo Facial Surg </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">~Sante Community Physicians-Fresno </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF">GI </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">4</td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Doris Stein Eye Research Center </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FAFAFA">Optometry/Opthomology </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">~Sante Community Physicians-Fresno </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF">Physical Therapy </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">5</td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Natural Vision - Madera </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FAFAFA">Optometry/Opthomology </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Advantek Benefit Administrator </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF">Radiology </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">6</td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Unknown </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF">Radiology </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Advantek Benefit Administrator </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF">(Not Specified) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">7</td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Physcians Imaging </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF">Radiology </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Advantek Benefit Administrator </td><td rowspan="1" colspan="1" width="211.5" height="51" align = "left" valign = "center" bgcolor ="#FFFFFF">Radiology </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">8</td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FF99CC">Gastroenterology and Hepatology, Farooqi, Saadat MD </td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FFFFFF">GI </td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FF99CC">Aetna Choice POS II </td><td rowspan="1" colspan="1" width="211.5" height="76.5" align = "left" valign = "center" bgcolor ="#FFFFFF">GI </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">A</td><td align="center" bgcolor="#C0C0C0">B</td><td align="center" bgcolor="#C0C0C0">C</td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">2</td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC">Spec Name </td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#FF99CC">Specialty </td><td rowspan="1" colspan="1" width="684" height="51" align = "left" valign = "center" bgcolor ="#CCFFCC">Data Pull </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">3</td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC">CRMC- Oral Maxillofacial Clinic </td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#FAFAFA">Oral and Maxo Facial Surg </td><td rowspan="1" colspan="1" width="684" height="102" align = "left" valign = "center" bgcolor ="#CCFFCC">=VLOOKUP($A3,Sheet1!$A:$D,3,FALSE) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">4</td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">Doris Stein Eye Research Center </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA">Optometry/Opthomology </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">=VLOOKUP($A4,Sheet1!$A:$D,3,FALSE) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">5</td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">Nguyen, Houng </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA">Endocrinology </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">=VLOOKUP($A5,Sheet1!$A:$D,3,FALSE) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">6</td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">Natural Vision - Madera </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA">Optometry/Opthomology </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">=VLOOKUP($A6,Sheet1!$A:$D,3,FALSE) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">7</td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">Kaye, David MD </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA">Optometry/Opthomology </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">=VLOOKUP($A7,Sheet1!$A:$D,3,FALSE) </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">8</td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">Kaweah Delta (Exeter Health Clinic </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#FAFAFA">Cardiology </td><td rowspan="1" colspan="1" width="684" height="30" align = "left" valign = "center" bgcolor ="#CCFFCC">=VLOOKUP($A8,Sheet1!$A:$D,3,FALSE) </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 · Jun 4, 2014

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 · Jun 4, 2014

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

most welcome.

Lookups and Cross Referencing - QUESTION PLEEAAASE HELP

brandonhauk

New Member

shadow12345

Well-known Member

Excel Facts

brandonhauk

New Member

shadow12345

Well-known Member

brandonhauk

New Member

shadow12345

Well-known Member

brandonhauk

New Member

shadow12345

Well-known Member

Similar threads

Forum statistics

Share this page

Lookups and Cross Referencing - QUESTION PLEEAAASE HELP

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock