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>

REF SOURCE	SPECIALTY	INSUR	REF FOR	Spec Name	Specialty	Data Pull
CRMC- Oral Maxillofacial Clinic	Oral and Maxo Facial Surg	~Sante Community Physicians-Fresno	GI	CRMC- Oral Maxillofacial Clinic	Oral and Maxo Facial Surg	"Insurance(s) here"
Doris Stein Eye Research Center	Optometry/Opthomology	~Sante Community Physicians-Fresno	Physical Therapy	Doris Stein Eye Research Center	Optometry/Opthomology
Natural Vision - Madera	Optometry/Opthomology	Advantek Benefit Administrator	Radiology	Nguyen, Houng	Endocrinology
Unknown	Radiology	Advantek Benefit Administrator	(Not Specified)	Natural Vision - Madera	Optometry/Opthomology
Physcians Imaging	Radiology	Advantek Benefit Administrator	Radiology	Kaye, David MD	Optometry/Opthomology
Gastroenterology and Hepatology, Farooqi, Saadat MD	GI	Aetna Choice POS II	GI	Kaweah Delta (Exeter Health Clinic	Cardiology
SHEET 1	SHEET 2

<TBODY> </TBODY>

</BODY></HTML>