Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2388662%22%20slang%3D%22en-US%22%3EVlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388662%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20an%20issue%20with%20VLOOKUP%20function%20where%20it%20duplicates%20information%20from%20an%20upper%20row%20even%20thought%20the%20lookup%20value%20is%20different.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2388662%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388716%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388716%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064072%22%20target%3D%22_blank%22%3E%40PatrycjaML%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20Calculation%20Options%20on%20the%20Formulas%20tab%20of%20the%20ribbon%20is%20set%20to%20Automatic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2389014%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389014%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BYes%20this%20is%20preset%20as%20automatic.%26nbsp%3B%20This%20issue%20appears%20with%20this%20setting.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20other%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello All.

 

I am having an issue with VLOOKUP function where it duplicates information from an upper row even thought the lookup value is different. 

Any ideas 

4 Replies

@PatrycjaML 

Make sure that Calculation Options on the Formulas tab of the ribbon is set to Automatic.

@Hans Vogelaar Yes this is preset as automatic.  This issue appears with this setting. 

Any other ideas?

@PatrycjaML 

Could you attach a copy of your workbook, or a sample workbook that demonstrates the problem?

@PatrycjaML 

 

VLOOKUP, unless you set the "range lookup" to FALSE or 0 will, if the column being used as reference is not sorted in alphabetical or sequential order, once it hits an "answer" that is beyond the desired one,  will go back to the previous.

 

For example, you have entered =VLOOKUP("C",tablerange,2) but the table being referenced is arrayed

A,10

B,34

D,34

E,23

C,67

then you will get the values next to "B" i.e., 34

If on the other hand you write your formula as =VLOOKUP("C",tablerange,2,0) you'll get your desired result. That last little 0 (or the word FALSE) is critical