Excel VLookup bug

%3CLINGO-SUB%20id%3D%22lingo-sub-1259002%22%20slang%3D%22en-US%22%3EExcel%20VLookup%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259002%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Team!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20problems%20with%20a%20german%20excel%20table%2C%20which%20has%20over%201.000%20rows%20of%20customer%20data%20and%20a%20VLOOKUP%20formula%20that%20should%20get%20data%20out%20of%20it.%3C%2FP%3E%3CP%3EA%20Microsoft%20Office%20Support%20employee%20took%20an%20hour%20to%20help%20me%20but%20then%20he%20gave%20me%20the%20link%20to%20this%20support%20page%2C%20as%20there%20seems%20to%20be%20an%20bug%20which%20he%20couldn%C2%B4t%20resolve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20V%20Lookup%20formula%20works%20just%20perfect%20until%20a%20specific%20row%2C%20after%20that%20row%2C%20or%20sometimes%20even%20after%20different%20rows%2C%20it%20gives%20out%20wrong%20data%20from%20a%20complete%20different%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20right%20here%20for%20help%20regarding%20this%20bug%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot!%20Best%20regards%2C%3C%2FP%3E%3CP%3EJulian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1259002%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259023%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLookup%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259023%22%20slang%3D%22en-US%22%3EHi%20Julian%2C%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20please%20upload%20a%20(anonimized)%20copy%20of%20your%20file%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259048%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLookup%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259048%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jan%2C%20thanks%20so%20much%20for%20your%20fast%20reply!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20anonimize%20the%20over%201.000%20customer%20names%20and%20dates%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259126%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLookup%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259126%22%20slang%3D%22en-US%22%3EIn%20the%20name%20column%2C%20in%20the%20first%20cell%20with%20a%20name%20just%20type%20Name%201%20and%20press%20enter.%20Then%20double-click%20the%20fill%20handle%20to%20fill%20that%20down.%20Now%20that%20the%20names%20are%20%22gone%22%20I%20expect%20there%20isn't%20much%20point%20in%20changing%20the%20dates.%20You%20can%20do%20the%20same%20for%20any%20other%20textual%20column%20(like%20with%20addresses%2C%20just%20type%20Street%201%20and%20fill%20that%20down).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259169%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLookup%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%C2%B4s%20an%20easy%20solution%20I%20didn%C2%B4t%20think%20about%20that%2C%20thank%20you!%3C%2FP%3E%3CP%3EAttached%20the%20table%2C%20when%20you%20enter%20name%201%2C%20it%20should%20show%20street%201%2C%20etc.%3C%2FP%3E%3CP%3EBut%20when%20entering%20for%20example%20name%20995%2C%20it%20shows%20a%20complete%20different%20street.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThnaks%20for%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259186%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLookup%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F597792%22%20target%3D%22_blank%22%3E%40Julian0706%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMost%20common%20error%20in%20VLOOKUP().%20You%20didn't%20put%20in%20the%20fourth%20parameter%20Bereich_Verweis.%26nbsp%3BExcel%20then%20assumes%20TRUE%20(WAHR)%20but%20it%20must%20be%20FALSE%20(FALSCH).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DWENN(ISTNV(SVERWEIS(B2%3BKundendaten!%24A%242%3AKundendaten!%24D%241986%3B2%3BFALSCH))%3B%22%20%22%3BSVERWEIS(B2%3BKundendaten!%24A%242%3AKundendaten!%24D%241986%3B2%3BFALSCH))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1259264%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLookup%20bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1259264%22%20slang%3D%22en-US%22%3EDetlef%20is%20right%2C%20please%20use%20the%20fourth%20argument%20of%20the%20vlookup%20function.%20In%20your%20case%20it%20must%20be%20FALSE.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Dear Team!

 

I have problems with a german excel table, which has over 1.000 rows of customer data and a VLOOKUP formula that should get data out of it.

A Microsoft Office Support employee took an hour to help me but then he gave me the link to this support page, as there seems to be an bug which he couldn´t resolve.

 

The V Lookup formula works just perfect until a specific row, after that row, or sometimes even after different rows, it gives out wrong data from a complete different row.

 

Am I right here for help regarding this bug?

 

Thanks a lot! Best regards,

Julian

6 Replies
Highlighted
Hi Julian,

Can you please upload a (anonimized) copy of your file?
Highlighted

Hi Jan, thanks so much for your fast reply!

 

How can I anonimize the over 1.000 customer names and dates?

Highlighted
In the name column, in the first cell with a name just type Name 1 and press enter. Then double-click the fill handle to fill that down. Now that the names are "gone" I expect there isn't much point in changing the dates. You can do the same for any other textual column (like with addresses, just type Street 1 and fill that down).
Highlighted

@Jan Karel Pieterse 

That´s an easy solution I didn´t think about that, thank you!

Attached the table, when you enter name 1, it should show street 1, etc.

But when entering for example name 995, it shows a complete different street.

 

Thnaks for the help!

Highlighted

@Julian0706 

Most common error in VLOOKUP(). You didn't put in the fourth parameter Bereich_Verweis. Excel then assumes TRUE (WAHR) but it must be FALSE (FALSCH).

 

=WENN(ISTNV(SVERWEIS(B2;Kundendaten!$A$2:Kundendaten!$D$1986;2;FALSCH));" ";SVERWEIS(B2;Kundendaten!$A$2:Kundendaten!$D$1986;2;FALSCH))

 

 

 

Highlighted
Detlef is right, please use the fourth argument of the vlookup function. In your case it must be FALSE.