SOLVED

Sverweis / VLookup function

%3CLINGO-SUB%20id%3D%22lingo-sub-934202%22%20slang%3D%22en-US%22%3ESverweis%20%2F%20VLookup%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EHi%2C%20I%20am%20finding%20it%20difficult%20to%20understand%20the%20steps%20I%20need%20to%20undertake%20to%20use%20the%20Sverweis%2FVlookup%20function%20on%20Microsoft%20Excel.%20I%20do%20not%20understand%20what%20fields%20have%20to%20be%20selected%20at%20what%20point.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EI%20have%20two%20lists%20which%20contain%20the%20same%20items%20however%20one%20list%20has%20the%20correct%20quantities%20and%20the%20other%20has%20the%20wrong%20quantity%2F%20no%20quantity%20and%20is%20in%20a%20different%20order%20to%20my%20first%20list.%20How%20can%20I%20get%20the%20correct%20quantity%20next%20to%20the%20correct%20item%20name%20on%20the%20second%20list%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EMany%20thanks%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-934202%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934366%22%20slang%3D%22en-US%22%3ERe%3A%20Sverweis%20%2F%20VLookup%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F431621%22%20target%3D%22_blank%22%3E%40fpanahi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Einsert%20a%20column%20next%20to%20your%20SKU%20list%20where%20you%20want%20to%20bring%20the%20correct%20qty.%20in%20first%20cell%20use%20this%20formula%20then%20drag%20it%20down%20to%20copy.%3C%2FP%3E%3CP%3E%3DIFERROR(VLOOKUP(%24A2%2CO%3AP%2C2%2C0)%2C%22Not%20in%20List%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewill%20return%20comment%20%22Not%20in%20list%22%20where%20your%20SKU%20is%20not%20in%20list%20of%20SKUs%20with%20correct%20quantity%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934482%22%20slang%3D%22en-US%22%3ERe%3A%20Sverweis%20%2F%20VLookup%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F426283%22%20target%3D%22_blank%22%3E%40Kashibaba%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehey%20thanks%2C%20I%20tried%20it%20but%20the%20formula%20doesnt%20seem%20to%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934635%22%20slang%3D%22en-US%22%3ERe%3A%20Sverweis%20%2F%20VLookup%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934635%22%20slang%3D%22en-US%22%3E%3CP%3Ebasically%20VLOOKUP%20formula%20has%204%20variables%3A%20VLOOKUP(lookup_value%2C%3B%20table%20array%2C%20column%20number%2C%20range%20lookup)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELookup_Value%3A%20the%20value%20you%20are%20searching%20for%20in%20the%20very%20first%20column%20of%20the%20table%20you%20have%20selected.%20This%20can%20be%20a%20text%2C%20value%20or%20a%20cell%20address%20where%20your%20value%20resides.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable_Array%3A%20this%20is%20the%20table%20where%20you%20are%20searching%20your%20value%20and%20returning%20another%20value%20from%20the%20same%20row%20in%20a%20different%20column.%20So%20this%20table%20must%20start%20from%20the%20column%20where%20your%20search%20value%20is%20in%20and%20end%20in%20the%20column%20where%20your%20return%20value%20is%20stored.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20Number%3A%20this%20is%20the%20number%20of%20the%20column%20where%20the%20value%20you%20want%20to%20get%20resides.%20starting%20from%201.%20in%20fact%201%20is%20the%20column%20where%20your%20search%20value%20is%20in.%20and%20the%20upper%20limit%20is%20defined%20by%20your%20table%20column%20number%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Erange%20lookup%3A%20Basically%20you%20set%20it%20to%26nbsp%3B%200%20to%20tell%20vlookup%20to%20find%20the%20exact%20value%20that%20you%20are%20searching.%20If%20you%20are%20searching%20in%20a%20sorted%20list%20of%20values%20you%20can%20set%20this%201%20for%20a%20close%20match%20which%20would%20be%20the%20closest%20number%20which%20is%20lower%20than%20your%20search%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20in%20your%20situation%20your%20search%20value%20is%20in%20B%20cloumn%20and%20your%20return%20value%20is%20in%20P%20column.%20So%20your%20table%20array%20shuld%20be%20B1%3AP6000%20(for%20example).%20and%20your%20column%20number%20must%20be%2015%20as%20P%20column%20is%20the%2015th%20column%20in%20your%20table.%20your%20search%20value%20depends%20on%20you%2C%20you%20can%20enter%20it%20as%20a%20text%20manually%20in%20the%20formula%20like%20%22PT1-BR-3130-230x160%22%20or%20prepare%20another%20table%20with%20all%20tha%20values%20in%20first%20column%20and%20vlookup%20formula%20in%20the%20second%20which%20refenreces%20first%20column%20as%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934721%22%20slang%3D%22en-US%22%3ERe%3A%20Sverweis%20%2F%20VLookup%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934721%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F431621%22%20target%3D%22_blank%22%3E%40fpanahi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F142487i39C4A38C61729D74%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20attached%20the%20file%20with%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWyn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934796%22%20slang%3D%22en-US%22%3ERe%3A%20Sverweis%20%2F%20VLookup%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934796%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%20really%20appreciate%20it%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, I am finding it difficult to understand the steps I need to undertake to use the Sverweis/Vlookup function on Microsoft Excel. I do not understand what fields have to be selected at what point. 

I have two lists which contain the same items however one list has the correct quantities and the other has the wrong quantity/ no quantity and is in a different order to my first list. How can I get the correct quantity next to the correct item name on the second list?

 

Many thanks 

5 Replies

@fpanahi 

insert a column next to your SKU list where you want to bring the correct qty. in first cell use this formula then drag it down to copy.

=IFERROR(VLOOKUP($A2,O:P,2,0),"Not in List")

 

will return comment "Not in list" where your SKU is not in list of SKUs with correct quantity

@Kashibaba 

hey thanks, I tried it but the formula doesnt seem to work.

basically VLOOKUP formula has 4 variables: VLOOKUP(lookup_value,; table array, column number, range lookup)

 

Lookup_Value: the value you are searching for in the very first column of the table you have selected. This can be a text, value or a cell address where your value resides.

 

Table_Array: this is the table where you are searching your value and returning another value from the same row in a different column. So this table must start from the column where your search value is in and end in the column where your return value is stored.

 

Column Number: this is the number of the column where the value you want to get resides. starting from 1. in fact 1 is the column where your search value is in. and the upper limit is defined by your table column number

 

range lookup: Basically you set it to  0 to tell vlookup to find the exact value that you are searching. If you are searching in a sorted list of values you can set this 1 for a close match which would be the closest number which is lower than your search value.

 

so in your situation your search value is in B cloumn and your return value is in P column. So your table array shuld be B1:P6000 (for example). and your column number must be 15 as P column is the 15th column in your table. your search value depends on you, you can enter it as a text manually in the formula like "PT1-BR-3130-230x160" or prepare another table with all tha values in first column and vlookup formula in the second which refenreces first column as value.

 

Best Response confirmed by fpanahi (Occasional Contributor)
Solution

Hi @fpanahi 

 

image.png

 

I've attached the file with formula

 

Wyn

Hi @Wyn Hopkins 

 

Thank you so much! really appreciate it