Vlookup/if statement??? Need help before i can record a macro

I use vlookup to bring over info from other reports. The cells in column A will represent a Stockroom as I show below. Column B will be part numbers. I want to do a vlookup that will basically say if column A equals "EEI" then search in this other worksheets table array but only for rows that have "EEI" in column A. Then it would need to repeat that same formula through the rest of the rows where the "IF" would change based on what's in column A of my new worksheet and the table array in the "other" worksheet is dependent on what's in column A of that other worksheet also.

So basically, I know this isn't the formula which is why I need help.....

If A7 (worksheet 1) is equal to "value" then, vlookup B7, (worksheet 2) If A7 is equal to "value" B7:B1012, 3, False. **But I need in the same formula--- If A7 (worksheet 1) is equal to "2nd value" then, vlookup B7, (worksheet 2) If A7 is equal to "2nd value" B7:B1012, 3, False.*** The "value" will change multiple times.

Can all of this go into one formula?

I'll be happy to provide as much detail in addition to this that's needed. I have to get this solved so I can record a macro to automate a weekly report.

Thanks in advance!!!

I'll test Young_86's response tonight. If I can't figure it out I'll supply the info needed by Harribone tonight also.

Thanks for the very quick responses!!!

Ok. So something I'm doing is not working out for me.

Below are screenshots and descriptions of what I'm trying to do.

This is my weekly report. Each week I start a new tab and import my data for the current week and leave last week's data on a separate tab.
1.JPG

Then I start my vlookup. In column A there is a stockroom for each part. That basically states where the part is. My vlookup is set to us B6 as the starting point so I'm searching for the part number. But I have to do a vlookup per stockroom in column A because the same part could be multiple places. I'm trying to search "table array" in the April 29th worksheet (previous week) to pull in data for columns E, F, and G. I use column D as my vlookup formula cells.
vlookup1.JPG

The April 29th sheet has the same info layout but it has the data I need for the blank columns above. So on this sheet I'm grabbing the data from D, E, and F.
2.JPG

As you can see in the images, I've made the text in column A red. That is what I need the formula to be dependent on. So and example: if A on the (may 20th) sheet is "EEI", then perform the vlookup using the info (table array) on the (april 29th) sheet but only if column A (April 29th) also is "EEI". Within the same formula I'd need to allow "EEI" to change both places to "LAB" and any other stockroom set up.

The number of rows changes week to week based on new stock which is why I need this formula to be specific to looking at the data in column A versus selecting a specific range. I do this manually each week now by selecting each range (based on column A) individually. But I'm manually doing this. If I automate this, Excel wouldn't know where to stop if I cannot make it dependent on A.

Let me know if this is enough info to help figure out the formula.

Thanks again!!!!!

Harribone:

Thank you, thank you, thank you!!! This worked perfectly. I'm not concerned with it being at the end. I can record the macro to delete those columns once done with calculating the vlookup. This just saved me major time that I spend each week. Was the only missing piece I could not figure out!!

Thank you very much for the detailed response.