Forum Discussion
Very odd VLOOKUP problem
Hello
I have a very odd problem with the VLOOKUP.
I have a workbook where i have some data for importing to my Wordpress website. I needed to add a column for taxonomy, so i added author name taxonomy and id in columns after the columns with my data for import.
This is my VLOOKUP, which works almost fine: =VLOOKUP(K2;P2:Q712;2;FALSE)
For some wierd reason, the first 5 cells it won't match, but the rest is not a problem. I have checked everything. It's general formatting, have tryed changing it to numbers, nothing changed. Looked for odd extra spaces, none. And when i change the number to get from cell number 6 (which is 7, there's no ID:2) it works just fine. SO it's only the numbers/ID's from the first 5 cells which has the problem. Entering any of them returns "Value not available".
EDIT: And no, it's not the danish characters, there's other authors on the list which works just fine.
What can be the problem ?
All the best
Carsten
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
4 Replies
- 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
