SOLVED

Very odd VLOOKUP problem

%3CLINGO-SUB%20id%3D%22lingo-sub-3073257%22%20slang%3D%22en-US%22%3EVery%20odd%20VLOOKUP%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073257%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20very%20odd%20problem%20with%20the%20VLOOKUP.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20where%20i%20have%20some%20data%20for%20importing%20to%20my%20Wordpress%20website.%20I%20needed%20to%20add%20a%20column%20for%20taxonomy%2C%20so%20i%20added%20author%20name%20taxonomy%20and%20id%20in%20columns%20after%20the%20columns%20with%20my%20data%20for%20import.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20VLOOKUP%2C%20which%20works%20almost%20fine%3A%26nbsp%3B%3DVLOOKUP(K2%3BP2%3AQ712%3B2%3BFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20wierd%20reason%2C%20the%20first%205%20cells%20it%20won't%20match%2C%20but%20the%20rest%20is%20not%20a%20problem.%20I%20have%20checked%20everything.%20It's%20general%20formatting%2C%20have%20tryed%20changing%20it%20to%20numbers%2C%20nothing%20changed.%20Looked%20for%20odd%20extra%20spaces%2C%20none.%20And%20when%20i%20change%20the%20number%20to%20get%20from%20cell%20number%206%20(which%20is%207%2C%20there's%20no%20ID%3A2)%20it%20works%20just%20fine.%20SO%20it's%20only%20the%20numbers%2FID's%20from%20the%20first%205%20cells%20which%20has%20the%20problem.%20Entering%20any%20of%20them%20returns%20%22Value%20not%20available%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20can%20be%20the%20problem%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20the%20best%3C%2FP%3E%3CP%3ECarsten%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel_Error.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342792i231F2F5DFEE0DB38%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel_Error.PNG%22%20alt%3D%22Excel_Error.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073257%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3073265%22%20slang%3D%22en-US%22%3ERe%3A%20Very%20odd%20VLOOKUP%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073265%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1288112%22%20target%3D%22_blank%22%3E%40Boblebad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20the%20formula%20in%20L2%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(K2%3B%24P%242%3A%24Q%24712%3B2%3BFALSE)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

Excel_Error.PNG

4 Replies

@Boblebad 

Change the formula in L2 to

 

=VLOOKUP(K2;$P$2:$Q$712;2;FALSE)

 

Then fill down.

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
best response confirmed by Boblebad (New Contributor)
Solution

@Boblebad 

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 

@Hans Vogelaar 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.