Vlookup Formula That Will Search for Value One Cell to the Right

isaacbro

New Member
Joined
Sep 29, 2011
Messages
5
I've been searching for hours and gave up which brought me to posting in this forum. Hopefully I'll find some help and become a regular user here!

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).

Its late, I'm tired... maybe this didn't make any sense?

P.S. I hope not to write a macro since the only macros I can do are recorded!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Is there a way if the sheet names do not match the vendor names? There are other users are anti-effecient and like to make things difficult. If its easier to convince them to let me change the sheet names I could go that route, I just don't want to get into it.
 
Upvote 0
Is there a way if the sheet names do not match the vendor names? There are other users are anti-effecient and like to make things difficult. If its easier to convince them to let me change the sheet names I could go that route, I just don't want to get into it.

You could offer the user the sheet names by means of a data-validated cell...
 
Upvote 0

Forum statistics

Threads
1,224,564
Messages
6,179,543
Members
452,924
Latest member
JackiG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top