linked sheets - trouble matching data

[Chelrie]

I have two files: 1) PO 2) SITE SUMMARY

I am working in PO and referencing SITE SUMMARY.

In PO

<table style="border-collapse: collapse; width: 194pt;" width="258" border="0" cellpadding="0" cellspacing="0"><col style="width: 17pt;" width="23"> <col style="width: 83pt;" width="110"> <col style="width: 94pt;" width="125"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 17pt;" width="23" height="20">
</td> <td class="xl66" style="width: 83pt;" width="110">A</td> <td class="xl66" style="width: 94pt;" width="125">B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">7</td> <td class="xl68">To</td> <td class="xl69" style="border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">8</td> <td class="xl68" style="border-top: medium none;">Email
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">9</td> <td class="xl68" style="border-top: medium none;">Address</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">10</td> <td class="xl68" style="border-top: medium none;">City, State ZIP</td> <td class="xl69" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>

in SITE SUMMARY:

<table style="border-collapse: collapse; width: 720pt;" width="956" border="0" cellpadding="0" cellspacing="0"><col style="width: 23pt;" width="30"> <col style="width: 77pt;" width="102"> <col style="width: 131pt;" width="174"> <col style="width: 98pt;" width="130"> <col style="width: 80pt;" width="106"> <col style="width: 72pt;" width="96"> <col style="width: 129pt;" width="172"> <col style="width: 110pt;" width="146"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 23pt;" width="30" height="20">
</td> <td class="xl70" style="width: 77pt;" width="102">A</td> <td class="xl70" style="width: 131pt;" width="174">B</td> <td class="xl70" style="width: 98pt;" width="130">C</td> <td class="xl70" style="width: 80pt;" width="106">D</td> <td class="xl70" style="width: 72pt;" width="96">E</td> <td class="xl70" style="width: 129pt;" width="172">F</td> <td class="xl70" style="width: 110pt;" width="146">G</td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl69" style="height: 18.75pt;" height="25">
</td> <td class="xl67">COMPANY</td> <td class="xl67">EMAIL</td> <td class="xl67">OFFICE PHONE</td> <td class="xl67">CELL PHONE</td> <td class="xl67">FAX</td> <td class="xl67">STREET ADDRESS</td> <td class="xl67">CITY, STATE, ZIP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">1</td> <td class="xl65">IS 1 Contact
</td> <td class="xl68">IS1 email
</td> <td class="xl66">(804) 749-3737</td> <td class="xl66">(804) 514-7893</td> <td class="xl66">(804) 749-3765</td> <td class="xl65">11214 Howards Mill Road</td> <td class="xl65">Glen Allen, VA 23059</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">2</td> <td class="xl65">IS 2 Contact</td> <td class="xl65">IS 2 Email</td> <td class="xl65">IS 2 Office Phone</td> <td class="xl65">IS 2 Cell</td> <td class="xl65">IS 2 Fax</td> <td class="xl65">IS 2 Street Address</td> <td class="xl65">IS 2 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">3</td> <td class="xl65">IS 3 Contact</td> <td class="xl65">IS 3 Email</td> <td class="xl65">IS 3 Office Phone</td> <td class="xl65">IS 3 Cell</td> <td class="xl65">IS 3 Fax</td> <td class="xl65">IS 3 Street Address</td> <td class="xl65">IS 3 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">4</td> <td class="xl65">IS 4 Contact</td> <td class="xl65">IS 4 Email</td> <td class="xl65">IS 4 Office Phone</td> <td class="xl65">IS 4 Cell</td> <td class="xl65">IS 4 Fax</td> <td class="xl65">IS 4 Street Address</td> <td class="xl65">IS 4 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">5</td> <td class="xl65">IS 5 Contact</td> <td class="xl65">IS 5 Email</td> <td class="xl65">IS 5 Office Phone</td> <td class="xl65">IS 5 Cell</td> <td class="xl65">IS 5 Fax</td> <td class="xl65">IS 5 Street Address</td> <td class="xl65">IS 5 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">6</td> <td class="xl65">IS 6 Contact</td> <td class="xl65">IS 6 Email</td> <td class="xl65">IS 6 Office Phone</td> <td class="xl65">IS 6 Cell</td> <td class="xl65">IS 6 Fax</td> <td class="xl65">IS 6 Street Address</td> <td class="xl65">IS 6 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">7</td> <td class="xl65">IS 7 Contact</td> <td class="xl65">IS 7 Email</td> <td class="xl65">IS 7 Office Phone</td> <td class="xl65">IS 7 Cell</td> <td class="xl65">IS 7 Fax</td> <td class="xl65">IS 7 Street Address</td> <td class="xl65">IS 7 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">8</td> <td class="xl65">IS 8 Contact</td> <td class="xl65">IS 8 Email</td> <td class="xl65">IS 8 Office Phone</td> <td class="xl65">IS 8 Cell</td> <td class="xl65">IS 8 Fax</td> <td class="xl65">IS 8 Street Address</td> <td class="xl65">IS 8 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">9</td> <td class="xl65">IS 9 Contact</td> <td class="xl65">IS 9 Email</td> <td class="xl65">IS 9 Office Phone</td> <td class="xl65">IS 9 Cell</td> <td class="xl65">IS 9 Fax</td> <td class="xl65">IS 9 Street Address</td> <td class="xl65">IS 9 City, State, Zip</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">10</td> <td class="xl65">IS 10 Contact</td> <td class="xl65">IS 10 Email</td> <td class="xl65">IS 10 Office Phone</td> <td class="xl65">IS 10 Cell</td> <td class="xl65">IS 10 Fax</td> <td class="xl65">IS 10 Street Address</td> <td class="xl65">IS 10 City, State, Zip</td> </tr> </tbody></table>

I have PO B7 set as a data validation, so that only things in SITE SUMMARY column A are options.

If I want it so the other information matches and fills in...for example, I want B8 to equal the email from site summary for the appropriate company.

I tried this - no luck.

=INDEX('[SITE SUMMARY.xlsx]INSTALLERS'!B:B,MATCH(B7,'[SITE SUMMARY.xlsx]INSTALLERS'!$B:$B,0))

Any help would be appreciated.

 

[BrianB]

For email
=VLOOKUP($B$7,'SITE SUMMARY'!$A$1:$G$11,2,FALSE)

then change column 2 as necessary for the rest

 

[jbeaucaire]

Your original formula is the most robust approach and simply needs a little tweak to work properly:

=INDEX('[SITE SUMMARY.xlsx]INSTALLERS'!B:B, MATCH(B7, '[SITE SUMMARY.xlsx]INSTALLERS'!$A:$A, 0))

You're matching the value you place in B7 to column A.