Vlookup

NILON

New Member
Joined
Jul 30, 2010
Messages
1
I am working in an excel workbook where the first worksheet is the master worksheet followed by worksheets for each location. I want to do a VLOOKUP to match the the code in the first column of the master worksheet to the codes in the location worksheets (also in the first column). The data I want to pull into the master worksheet is in a different column in each of the location worksheets. Is there a way to setup the function so that it will pull from different columns in each worksheet without having to setup a different formula for each location in the master worksheet? For example, the information I need for location A is in column Q and the information needed for location B is in column S. I want to pull from the different columns using only one formula in the master worksheet.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
If you have the names of the worksheets as headers, and you want to use VLOOKUP, here's the formula you'd need to deploy -

Code:
=VLOOKUP(A2,INDIRECT(CONCATENATE("'",$B$1,"'!A:B"),1),COLUMN(B$1),0)
Assumptions -
1. B1 includes the sheet name;
2. A2 is the value you are looking up in each sheet
3. I have taken only two columns here (A:B). You need to change it to the number range of columns till where you have the data.
4. The column # will change automatically to the column where the formula is deployed.

Hope this helps !

Prabby
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,970
Messages
5,508,467
Members
408,686
Latest member
celobacara

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top