Forum Discussion
Very odd VLOOKUP problem
In your original formula in L2 =VLOOKUP(K2;P2:Q712;2;FALSE), the references to K2 and to P2:Q712 are relative. this means that Excel will adjust them automatically when you fill or copy L2 to other cells.
In L3 it becomes =VLOOKUP(K3;P3:Q713;2;FALSE), in L4 it will be =VLOOKUP(K4;P4:Q714;2;FALSE) etc.
The change from K2 to K3, K4, ... is exactly what you want, but the change from P2:Q712 to P3:Q713 etc. is not. It causes the first IDs to drop out of the range.
To prevent this, we add $ signs. They make the reference absolute. Excel will not change an absolute reference when you fill or copy the cell with the formula to other cells.
So the formula in L2 =VLOOKUP(K2;$P$2:$Q$712;2;FALSE) will become =VLOOKUP(K3;$P$2:$Q$712;2;FALSE) in L3, and =VLOOKUP(K4;$P$2:$Q$712;2;FALSE) in L4, etc.
The reference to K2 changes, as desired, but the reference to $P$2:$Q$712 does not change.
The easiest way to change P2:Q712 to $P$2:$Q$712 is to select it in the formula and to press F4.
For more info, see Switch between relative, absolute, and mixed references
- Thank you Hans. it worked 🙂
Can you tell me why ?
I really don't understand it, because i got it from a guide on how to do it. I have seen others where they use $ in front of all columns and cell-number. And why did it give me such a wierd error ?
I tried moving the numbers around and found out that if i deleted the first 1-entry and moved that maybe ten places down the list, then it work, but only for two row, and then the error again for the rest. I then loaded another file with more rows to test it on a completely new set of data; Same result. - But, number 46 fell of the wagon too this time 😮In your original formula in L2 =VLOOKUP(K2;P2:Q712;2;FALSE), the references to K2 and to P2:Q712 are relative. this means that Excel will adjust them automatically when you fill or copy L2 to other cells.
In L3 it becomes =VLOOKUP(K3;P3:Q713;2;FALSE), in L4 it will be =VLOOKUP(K4;P4:Q714;2;FALSE) etc.
The change from K2 to K3, K4, ... is exactly what you want, but the change from P2:Q712 to P3:Q713 etc. is not. It causes the first IDs to drop out of the range.
To prevent this, we add $ signs. They make the reference absolute. Excel will not change an absolute reference when you fill or copy the cell with the formula to other cells.
So the formula in L2 =VLOOKUP(K2;$P$2:$Q$712;2;FALSE) will become =VLOOKUP(K3;$P$2:$Q$712;2;FALSE) in L3, and =VLOOKUP(K4;$P$2:$Q$712;2;FALSE) in L4, etc.
The reference to K2 changes, as desired, but the reference to $P$2:$Q$712 does not change.
The easiest way to change P2:Q712 to $P$2:$Q$712 is to select it in the formula and to press F4.
For more info, see Switch between relative, absolute, and mixed references
HansVogelaar Thank you for the explanation 🙂
I saw that the Q-number added up, and thought that it might be a problem in some way, but couldn't see anything happen to that column. So therefor i didn't suspect it to be that which caused the problems in the L-column.
