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

 

[adam087]

Perhaps it might be helpful to see some (suitably anonymised) sample data? Tips for posting in my signature.

/AJ

 

[brandonhauk]

How do you post sample pictures, I feel so new to this, I dont know where anything is. LOL

 

[adam087]

I personally use MrExcel HTML Maker but there are other options out there...
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

/AJ

 

[shadow12345]

If you can post sample data that would help a lot... but you can do this with a vlookup by the sounds of it.

I assumed the following for the below. Doctors name is in Cell A2, on Sheet 1. Doctors insurance info is in Sheet2, and the first thing we want if the data in column B from Sheet 2....

=VLOOKUP($A$2,Sheet2!$A:$D,2,FALSE)

See below.

This was 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></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="237" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#333333">Name </font></td><td rowspan="1" colspan="1" width="351" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Insuance Number </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Office </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="237" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#333333">A doctor </font></td><td rowspan="1" colspan="1" width="351" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=VLOOKUP($A$2,Sheet2!$A:$C,2,FALSE) </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">=VLOOKUP($A$2,Sheet2!$A:$C,3,FALSE) </font></td></tr>
</table>

This was sheet2

<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="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#333333">Name </font></td><td rowspan="1" colspan="1" width="351" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Insuance Number </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Address </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">A doctor </font></td><td rowspan="1" colspan="1" width="351" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">123 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">a place </font></td></tr>
</table>


But if you give us an example of the data, it will be much easier to help.

 

[brandonhauk]

Thanks so much, seems to be down right now, but I will use that for sure. I am working on a html export for now.

 

[brandonhauk]

Thansk so much shadow12345 much appreciated working on the html images right now. I will try that formula and see what it brings back.

 

[shadow12345]

Thansk so much shadow12345 much appreciated working on the html images right now. I will try that formula and see what it brings back.

If you need me to breakdown what each part does, so you can change it to fit your situation let me know.

 

[brandonhauk]

ok here are the HTML links to what I would like to do:I want the insurance in sheet 1 shown left to populate in sheet 2 based on the 'ref source/specialty name) - I want them to recognize the match in name and shoot the correlating insurance into the data pull cell.file:///C:/Users/brandonh/AppData/Local/Temp/Example%20HTML%20table%20(ASAP%20Utilities).html Hopefully you can see it. B

 

[brandonhauk]

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

OK finally got it. I want the insurance in sheet 1 shown left to populate in sheet 2 based on the 'ref source/specialty name) - I want them to recognize the match in name and shoot the correlating insurance into the data pull cell<HTML><HEAD><TITLE>New Page</TITLE>****** content="text/html; charset=windows-1252" http-equiv=Content-Type>****** name=GENERATOR content="MSHTML 8.00.7601.18446"></HEAD><BODY>[TABLE="width: 721"]<TBODY>[TR][TD="bgcolor: #ff99cc"]REF SOURCE[/TD][TD="bgcolor: #ffcc99"]SPECIALTY[/TD][TD="bgcolor: #ff99cc"]INSUR[/TD][TD="bgcolor: #ffcc99"]REF FOR[/TD][TD="bgcolor: #ccffcc"]Spec Name[/TD][TD="bgcolor: #ff99cc"]Specialty[/TD][TD="bgcolor: #ccffcc"]Data Pull[/TD][/TR][TR][TD="bgcolor: #ff99cc"]CRMC- Oral Maxillofacial Clinic[/TD][TD]Oral and Maxo Facial Surg[/TD][TD="bgcolor: #ff99cc"]~Sante Community Physicians-Fresno[/TD][TD="bgcolor: #ffffff"]GI[/TD][TD="bgcolor: #ccffcc"]CRMC- Oral Maxillofacial Clinic[/TD][TD]Oral and Maxo Facial Surg[/TD][TD="bgcolor: #ccffcc"]"Insurance(s) here"[/TD][/TR][TR][TD="bgcolor: #ff99cc"]Doris Stein Eye Research Center[/TD][TD]Optometry/Opthomology[/TD][TD="bgcolor: #ff99cc"]~Sante Community Physicians-Fresno[/TD][TD="bgcolor: #ffffff"]Physical Therapy[/TD][TD="bgcolor: #ccffcc"]Doris Stein Eye Research Center[/TD][TD]Optometry/Opthomology[/TD][TD="bgcolor: #ccffcc"][/TD][/TR][TR][TD="bgcolor: #ff99cc"]Natural Vision - Madera[/TD][TD]Optometry/Opthomology[/TD][TD="bgcolor: #ff99cc"]Advantek Benefit Administrator[/TD][TD="bgcolor: #ffffff"]Radiology[/TD][TD="bgcolor: #ccffcc"]Nguyen, Houng[/TD][TD]Endocrinology[/TD][TD="bgcolor: #ccffcc"][/TD][/TR][TR][TD="bgcolor: #ff99cc"]Unknown[/TD][TD="bgcolor: #ffffff"]Radiology[/TD][TD="bgcolor: #ff99cc"]Advantek Benefit Administrator[/TD][TD="bgcolor: #ffffff"](Not Specified)[/TD][TD="bgcolor: #ccffcc"]Natural Vision - Madera[/TD][TD]Optometry/Opthomology[/TD][TD="bgcolor: #ccffcc"][/TD][/TR][TR][TD="bgcolor: #ff99cc"]Physcians Imaging[/TD][TD="bgcolor: #ffffff"]Radiology[/TD][TD="bgcolor: #ff99cc"]Advantek Benefit Administrator[/TD][TD="bgcolor: #ffffff"]Radiology[/TD][TD="bgcolor: #ccffcc"]Kaye, David MD[/TD][TD]Optometry/Opthomology[/TD][TD="bgcolor: #ccffcc"][/TD][/TR][TR][TD="bgcolor: #ff99cc"]Gastroenterology and Hepatology, Farooqi, Saadat MD[/TD][TD="bgcolor: #ffffff"]GI[/TD][TD="bgcolor: #ff99cc"]Aetna Choice POS II[/TD][TD="bgcolor: #ffffff"]GI[/TD][TD="bgcolor: #ccffcc"]Kaweah Delta (Exeter Health Clinic[/TD][TD]Cardiology[/TD][TD="bgcolor: #ccffcc"][/TD][/TR][TR][TD="colspan: 4"]SHEET 1[/TD][TD="colspan: 9"]SHEET 2[/TD][/TR]</TBODY>[/TABLE]</BODY></HTML>