Excel 2010 :: Lookup With Different Columns

On my "Order" sheet, I enter an account no. that looks up the company name, address, etc on my "Accounts" sheet. Below the address info on the "Accounts" sheet is a list of the salespersons with their email address.

After entering the account no., I want to enter the salespersons name and automatically look up their email address. I can make the lookup function work if I tell the formula which column the salesperson is located in, but am lost how to write the formula to locate the salesperson based upon the column no. that contains their company info.

It's not pretty, but this works.

=IF(F1"",HLOOKUP(F1,ACCOUNTS!A1:Z100,ORDER!G9, FALSE),"")

F1 - contains the account no. entered on the order sheet.

Accounts!A1:Z100 - range containing columns of account info.

ORDER!G9 - This is the row number value I obtained by using a match function to lookup the Salespersons name entered in cell F8, then I add 1 to render the value of the row that contains the email address located directly below the salespersons name.

=(MATCH(F8,ACCOUNTS!B1:B100,FALSE)+1)

Herein lies my issue, the salesperson name will not always be in Column B

I can write another Match to obtain the column no, but then I am using another cell to hold a calculated value for my lookup statement.

ACCOUNTSABCD1CODACCT1ACCT2ACCT323 COMPANY 1COMPANY 2COMPANY 34ADDRESS ADDRESS ADDRESS 5   CITY STATE ZIPCITY STATE ZIPCITY STATE ZIP6TELEPHONETELEPHONETELEPHONE78910TOM JONESJANE DOEMARY SMITH11tjones@company1.comjdoe@company2.commsmith@company3.com12SALESPERSON 2SALESPERSON 213salesperson2@company1.comsalesperson2@company2.com14SALESPERSON 315salesperson3@company1.com
Excel 2010

In words, Find Mary Smiths email address, she works at the company with the account number in cell F1 on the order sheet.