asked on

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

Professor J

yes,

you can merge vlookup with match function

Jagwarman

ASKER

could you provide an example please

Professor J

please see attached example
Book1.xlsx

Professor J

i have attached both version.

Vlookup version and also Index & match version

Jagwarman

ASKER

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

Professor J

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 Archibald

Do you mean something like this?
VLookup--1-.xlsx

Jagwarman

ASKER

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

Professor J

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

Jagwarman

ASKER

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.

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

Ah perfect many many thanks