Is this VLookup possible

I know the name of the column so lets call it Monday. Normally this will be in column B so in cell AA2 I would say something like =VLOOKUP(B2,Sheet2!F:I,4,0) but I am concerned that columns sometimes move. So lets say the column called Monday has moved to column F. is it possible to make the Lookup flexible so that I can always find the correct cell
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
yes,

you can merge vlookup with match function
JagwarmanAuthor Commented:
could you provide an example please
ProfessorJimJamMicrosoft Excel ExpertCommented:
please see attached example
Book1.xlsx
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

ProfessorJimJamMicrosoft Excel ExpertCommented:
i have attached both version.

Vlookup version and also Index & match version
JagwarmanAuthor Commented:
That's really good I will definitely be able to use that in the future but it's not what I am asking and that's probably my fault.
I have attached a file with the example.
You will see in Sheet 2 Monday is in Column A and I am doing the lookup in column D so I am saying =VLOOKUP(A2,Sheet4!A:B,2,0)

In sheet 3 Monday has moved to Column B so the vlookup no longer works.

In your example in sheet 1 you quote L20 and L23 but I can't Quote A2 because it is that that has moved to B2

hope this helps.
VLookup.xlsx
ProfessorJimJamMicrosoft Excel ExpertCommented:
well, when you copy formula the relative reference formula also changes.  the reason your formula in sheet 3 did not work is becuase you did not lock it the lookup table needs to be locked  like this Sheet4!$A:$B

if you copy this  =VLOOKUP(A2,Sheet4!$A:$B,2,0) to another sheet the locked lookup table Sheet4!$A:$B will not change
Rory ArchibaldCommented:
Do you mean something like this?
VLookup--1-.xlsx
JagwarmanAuthor Commented:
sorry I am still not making sense. my fault.

I understand the lock cells I only showed it on a different sheet to show the example that if Monday moves from one column to another it will not work.

Real scenario would be today user copies data onto sheet1 and Monday is in Column A.

Tomorrow User copies data onto Sheet1 and Monday is in Column B

So data is always in sheet1 but Monday moves from A to B [or X or W etc]

So the lookup cannot say =VLOOKUP(A2 ....... because tomorrow its in B2

So I think I need to say find Monday and the go to cell [row 2]

I hope I have explained myself more clearly this time.

Again, sorry for confusion
ProfessorJimJamMicrosoft Excel ExpertCommented:
Jagwarman

check the attached version.  with this one no mater where you move the column of lookup values i mean the Monday column the formula will work.

but you need to put the formula beneath the data and it cannot be parallel with rows of the lookup range.

please see example

=VLOOKUP(INDIRECT(ADDRESS(ROW(2:2),MATCH(LOOKUP(2,1/(1-ISBLANK(2:2)),2:2),2:2,0))),Sheet4!$A:$B,2,0)
VLookup.xlsx
JagwarmanAuthor Commented:
Thanks for all your help. It looks like what I am asking is not possible then because I need to put the formula on the same line.
ProfessorJimJamMicrosoft Excel ExpertCommented:
it is possible, see the attached version.  but know that your column of Monday can be either in any of the Columns of A, B , C  or D,  it cannot be beyond D, if you want to adjust it further then your formula must not be in between the range .

for example

=VLOOKUP(INDIRECT(ADDRESS(ROW(A2),MATCH(LOOKUP(2,1/(1-ISBLANK(A2:D2)),A2:D2),A2:D2,0))),Sheet4!$A:$B,2,0)

if you are to expand this further then change the D to further columns, but your cell in which you put the Vlookup must not be in between the A2:D2 or if you change it to A2:Z2 then cell having the formula should be beyond Z.
VLookup.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Ah perfect many many thanks
JagwarmanAuthor Commented:
brilliant Expert
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome.

thanks for your feedback and nice words.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.