VLOOKUP when Table Array Changes

[jb007]

Hi, I'm new here, and I have a question regarding a problem I ran into today, at work.

I am basically using the VLOOKUP function in a sheet (sheet1), to pull out data from another sheet(sheet2).

But on a regular basis, the Table Array from sheet2 is changed, because the data from sheet2 is downloaded from another source, and as a result, the Table Array I am interested in constantly moves to the right, as more and more information gathered.
However, I am pasting the new data in sheet2, so Excel can't rely on relative reference. And my formula does not work anymore.

I could always manually re-adjust the Table Array in the VLOOKUP function, but it takes some time, and this document needs to be easy to use for other colleagues as well.
So I want Excel to automatically find the column headers from sheet2(which are the same from sheet1) that I am interested in, and use that as the Table Array.
But how do I do just that ? Any suggestions would be great!

 

[oldbrewer]

=offset(sheet2!A1,match(A1, sheet2!A1:Z1,0),5) would find the name that is in sheet 1 A1 and then drop down 5 rows and return that cell value - would this sort of approach work?

 

[jb007]

Say =VLOOKUP(sheet1!A1,sheet2!B:D,MATCH(data!$D$1,sheet2!$B$1:$D$1,0),0) is my function. [It could also be VLOOKUP(sheet1!A1,sheet2!B:D,COLUMNS(sheet2!B:D),0)]


After pasting another content in sheet 2, the Table Array I am interested in moves from B:D to say E:G. (In reality I'm working with columns such as ABC, that's why I want the whole thing to be as automated as possible)


Now my sheet1A1 header would match the sheet2E1 header, and sheet1B1 would match sheet2G1.
What function could do the trick of finding the new E:G Table Array ?


The new function would look like this if written manually:
=VLOOKUP(sheet1!A1,sheet2!E:G,MATCH(data!$G$1,sheet2!$E$1:$G$1,0),0) [or =VLOOKUP(sheet1!A1,sheet2!E:G,COLUMNS(sheet2!E:G),0)


Note:
To me is irrelevant that the new Table Array is in E:G. I don't want to manually identify it!

 

[oldbrewer]

the headers move from B:D to E:G but match(Bheader,sheet2!A1:Z1,0) will always find it wherever it is

 

[jb007]

=offset(sheet2!A1,match(A1, sheet2!A1:Z1,0),5) would find the name that is in sheet 1 A1 and then drop down 5 rows and return that cell value - would this sort of approach work?

Only problem, I don't need to offset sheet2!A1, but rather the actual column that contains my header.
I can find it using the match function, but then how do I tell excel to look into that column ?

For example, say it's column C. Match will return 3. How can I work around that and tell excel to offset column C ?

Basically how can you turn that 3 into column C ?

 

[oldbrewer]

the headers move from B:D to E:G but match(Bheader,sheet2!A1:Z1,0) will always find it wherever it is

bheader is in sheet1 - you are looking for a match in row 1 on sheet2

offset will use the 3

 

[jb007]

=offset(sheet2!A1,match(A1, sheet2!A1:Z1,0),5) would find the name that is in sheet 1 A1 and then drop down 5 rows and return that cell value - would this sort of approach work?

first of all, my columns move from left to right.
So I don't need my range to drop down, but to move to right, 5 columns.

=VLOOKUP(A1,Sheet2!C:F,((MATCH($B$1,Sheet3!$1:$1,0))-MATCH($A$1,Sheet3!$1:$1,0))+1,0) -> this is what I ended up using. (Headers from sheet1 columns A and B are found in columns C and F in sheet2)

It's all automated, except that C:F part which is the Table Array, which I don't know how to implement in a different way, as for Excel to automatically determine it by some formula.
My next Sheet may contain the headers I am interested in columns W and X, doesn't matter. I need to tell Excel to look in W and X without changing anything in the formula.

Could you please make all the necessary adjustments in the formula, maybe I can't see your point.

 

[jb007]

Offset works assuming you already know your range and where it moved.
I do not. My new Table Array will be different in nr. of colums from my previous one.

 

[jb007]

Using =MATCH() to find the columns' number for the columns I am interested in, and then it must have something to do with =INDIRECT() and/or =ADDRESS() to go from that number to the actual column, but I can't seem to work it out just yet.

 

[jb007]

I managed to crack the code using both =INDIRECT() & =ADDRESS().

=VLOOKUP(A1,INDIRECT(ADDRESS(1,MATCH(A1,Sheet2!1:1,0),,,"Sheet3")&":"&ADDRESS(300,MATCH(B1,Sheet2!1:1,0))),((MATCH($B$1,Sheet3!$1:$1,0))-MATCH($A$1,Sheet3!$1:$1,0))+1,0)

With the function in green I'm basically setting the new Table Array bassed on the columns that contain the headers I am interested in. It's kind of a Dynamic Range.
Only thing I have to do is make sure the new sheet has the same name, in this case "Sheet2".

Hopefully this will be of some help for anybody in the future.