VLookup Formula That Will Search For Value One Cell To The Right

I get new worksheets on a regular basis which are of the same format (same columns with headers) and I have to perform both vlookups and sumifs on them. Their are about 12 different tabs I perform the vlookup on based a vendor name in A2 and its corresponding tab. I am tired of typing the same thing over and over again.

What I'd like to write is a Control Panel with all the equations so I can just copy/paste them in when I get a new sheet.

How can I write a vlookup which would search for the value which is one cell to the right of where I put the formula in?

Similar to:
=vlookup(current cell over one, 'vendor1sheet'!A:C,3,0).

I would then want to copy it down the worksheet. Currently type in column E everytime I get a new sheet:
=VLOOKUP(F2,'Vendor1'!A:C,3,0)

The catch

Column A has the vendor name, there are about 12 vendors, each with a different tab with related data which I am searching through.

Ultimately, I'd like to have an equation that would do the vlookup (and sumif) as described above and also use the vendor name in column A to look on a particular tab.

Something like:
=vlookup(one cell to the right,'VendorTabFromVendorNameInColumnA'!A:C,3,0)

I was going to use nested if statements on a control panel sheet with a column for the vendor name and a column for the vlookupformula and than paste a single formula all the way down column E:

In column E, every row:
If(A2=vendor1, 'controlpanel'!vendor1equation), if(A2=vendor2, 'controlpanel'!vendor2equation), etc.

Again, the vlookups would all check for one cell to the right of where this nested if statement was (located in column E, looking in F).