10-23-2019 03:47 AM
10-23-2019 03:47 AM
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?
10-23-2019 04:00 AM
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
10-23-2019 04:42 AM
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.