Excel beginner - VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-1331877%22%20slang%3D%22en-US%22%3EExcel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331877%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Can%20anyone%20see%20what%20is%20wrong%20with%20this%20formula%20which%20requires%20the%20value%20in%20G6%2C%20to%20be%20search%20in%20the%20previous%20sheet%26nbsp%3B%20Cells%20B17-B20%20and%20bring%20across%20the%20cell%20contents%20in%20the%20next%20column%20C17-C20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3DVLOOKUP('Environement%20de%20control'!G6%2C%20Aper%C3%A7u!B17%3AC20%2C2%2CFALSE)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThx%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1331877%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331885%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331885%22%20slang%3D%22en-US%22%3EBased%20the%20VLOOKUP%20formula%3CBR%20%2F%3E%3DVLOOKUP('Environement%20de%20control'!G6%2C%20Aper%C3%A7u!B17%3AC20%2C2%2CFALSE)%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20is%20need%20to%20apply%20Absolute%20Cell%20Reference%20to%20the%20Table%20Array%20second%20argument%20such%20as%3A%20%3DVLOOKUP('Environement%20de%20control'!G6%2C%20Aper%C3%A7u!%24B%2417%3A%24C%2420%2C2%2CFALSE)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331890%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331890%22%20slang%3D%22en-US%22%3EIf%20possible%20to%20attach%20the%20file%2C%20that%20will%20be%20fine%20to%20have%20better%20clarity%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331953%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAS%20requested%20-%20formula%20is%20on%20second%20sheet%20G7%3C%2FP%3E%3CP%3EThx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1331980%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1331980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634130%22%20target%3D%22_blank%22%3E%40MDV005%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFixed.%20See%20the%20attached%20solution.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_0-1587643249678.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F186102i1D981454E56C660E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_0-1587643249678.png%22%20alt%3D%22Abiola1_0-1587643249678.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332017%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332017%22%20slang%3D%22en-US%22%3ESorry%20-%20this%20didn't%20seem%20to%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332068%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332068%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332089%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634130%22%20target%3D%22_blank%22%3E%40MDV005%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWere%20you%20looking%20to%20do%20more%20with%20the%20VLOOKUP%3F%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%20's%20solution%20looks%20good.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332116%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332116%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20copied%20it%20but%20still%20get%20the%20error%20message%20-%20I%20wanted%20to%20know%20what%20changes%20he%20made...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332117%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634130%22%20target%3D%22_blank%22%3E%40MDV005%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20it%20shall%20be%20used%20semicolon%20instead%20of%20comma%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332157%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332157%22%20slang%3D%22en-US%22%3EThx%20-%20this%20was%20it%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332191%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332191%22%20slang%3D%22en-US%22%3ENot%20sure%20why%20the%20EXCEL%20help%20function%20gave%20me%20commas!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332210%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634130%22%20target%3D%22_blank%22%3E%40MDV005%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMy%20guess%20your%20main%20language%20for%20Excel%20is%20English%2C%20thus%20all%20help%20is%20also%20for%20English%20SKU.%20At%20the%20same%20your%20operation%20system%20locale%20is%20different.%20Assuming%20you%20are%20on%20Windows%20you%20may%20chcek%3C%2FP%3E%0A%3CP%3EWin%2BR%20-%26gt%3B%20%3CSTRONG%3Econtrol%20international%3C%2FSTRONG%3E%20-%26gt%3B%20advanced%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20394px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F186110i4BE47DC219C8D2FB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332244%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634130%22%20target%3D%22_blank%22%3E%40MDV005%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHello%2C%20what%20I%20did%20is%20to%20apply%20Absolute%20Cell%20Reference%20to%20the%20table_array%20second%20argument%20and%20copy%20down%20the%20formula.%20See%20the%20attached%20file%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20your%20function%20separator%20is%20semi%20colon%20(%3B)...%20Over%20here%20in%20English%2C%20it%20is%20comma%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1332308%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20beginner%20-%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634130%22%20target%3D%22_blank%22%3E%40MDV005%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat'll%20do%20it.%20Years%20ago%20I%20was%20working%20with%20a%20client%20that%20reported%20IF%20was%20%22broken%22%20in%20his%20newly%20upgraded%20Office%202007.%26nbsp%3B%20He%20was%20quite%20upset%20with%20Excel.%26nbsp%3B%20Turns%20out%20he%20had%20been%20messing%20with%20the%20regional%20settings...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, Can anyone see what is wrong with this formula which requires the value in G6, to be search in the previous sheet  Cells B17-B20 and bring across the cell contents in the next column C17-C20?

 

=VLOOKUP('Environement de control'!G6, Aperçu!B17:C20,2,FALSE)

 

Thx

14 Replies
Highlighted
Based the VLOOKUP formula
=VLOOKUP('Environement de control'!G6, Aperçu!B17:C20,2,FALSE)

There is need to apply Absolute Cell Reference to the Table Array second argument such as: =VLOOKUP('Environement de control'!G6, Aperçu!$B$17:$C$20,2,FALSE)
Highlighted
If possible to attach the file, that will be fine to have better clarity
Highlighted

@Abiola1 

 

AS requested - formula is on second sheet G7

Thx

Highlighted

@MDV005 

 

Fixed. See the attached solution.

Abiola1_0-1587643249678.png

 

Highlighted
Sorry - this didn't seem to help
Highlighted

 

 

 

@Abiola1 

Highlighted

@MDV005 

Were you looking to do more with the VLOOKUP? @Abiola1 's solution looks good.

Highlighted

Hi 

 

I copied it but still get the error message - I wanted to know what changes he made...

 

@Patrick2788 

Highlighted

@MDV005 

Most probably it shall be used semicolon instead of comma

Highlighted
Thx - this was it
Highlighted
Not sure why the EXCEL help function gave me commas!!
Highlighted

@MDV005 

My guess your main language for Excel is English, thus all help is also for English SKU. At the same your operation system locale is different. Assuming you are on Windows you may chcek

Win+R -> control international -> advanced

image.png

Highlighted

@MDV005 

 

Hello, what I did is to apply Absolute Cell Reference to the table_array second argument and copy down the formula. See the attached file

 

I guess your function separator is semi colon (;)... Over here in English, it is comma

 

 

Highlighted

@MDV005 

That'll do it. Years ago I was working with a client that reported IF was "broken" in his newly upgraded Office 2007.  He was quite upset with Excel.  Turns out he had been messing with the regional settings...