 SOLVED

# Sverweis / VLookup function

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

# Re: Sverweis / VLookup function

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

# Re: Sverweis / VLookup function

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

# Re: Sverweis / VLookup function

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

# Re: Sverweis / VLookup function

Hi @fpanahi I've attached the file with formula

Wyn

# Re: Sverweis / VLookup function

Thank you so much! really appreciate it