Using the VLOOKUP function to substitute values

Who is too lazy or does not have time to read – watch the video. Details and nuances are in the text below.

Formulation of the problem

So, we have two tables – order table и price list:

The task is to substitute prices from the price list into the table of orders automatically, focusing on the name of the product so that later you can calculate the cost.

Solution

In the Excel function set, under the category References and arrays (Lookup and reference) there is a function VPR (VLOOKUP).This function looks for a given value (in our example, this is the word “Apples”) in the leftmost column of the specified table (price list) moving from top to bottom and, having found it, displays the contents of the adjacent cell (23 rubles) .Schematically, the operation of this function can be represented So:

For ease of further use of the function, do one thing at once – give the range of cells in the price list your own name. To do this, select all the cells of the price list except for the “header” (G3: H19), select from the menu Insert – Name – Assign (Insert — Name — Define) or press CTRL+F3 and enter any name (no spaces) like Price… Now, in the future, you can use this name to link to the price list.

Now we use the function VPR… Select the cell where it will be entered (D3) and open the tab Formulas – Function Insertion (Formulas — Insert Function)… In the category References and arrays (Lookup and Reference) find the function VPR (VLOOKUP) and press OK… A window for entering arguments for the function will appear:

Using the VLOOKUP function to substitute values

We fill them in turn:

  • Desired value (Lookup Value) – the name of the product that the function should find in the leftmost column of the price list. In our case, the word “Apples” from cell B3.
  • Table (Table Array) – a table from which the desired values ​​uXNUMXbuXNUMXbare taken, that is, our price list. For reference, we use our own name “Price” given earlier. If you didn’t give a name, you can just select the table, but don’t forget to press the button F4to pin the link with dollar signs, because otherwise, it will slide down when copying our formula down to the rest of the cells in column D3:D30.
  • Column_number (Column index number) – serial number (not a letter!) Of the column in the price list from which we will take price values. The first column of the price list with the names is numbered 1, therefore we need the price from the column numbered 2.
  • interval_lookup (Range Lookup) – only two values ​​can be entered in this field: FALSE or TRUE:
      • If a value is entered 0 or LYING (FALSE), then in fact this means that only search is allowed exact match, i.e. if the function does not find the non-standard item specified in the order table in the price list (if “Coconut” is entered, for example), it will generate the #N/A (no data) error.
      • If a value is entered 1 or TRUE (TRUE), then this means that you allow the search not for the exact, but approximate match, i.e. in the case of “coconut”, the function will try to find a product with a name that is as close as possible to “coconut” and return the price for this name. In most cases, such an approximate substitution can play a trick on the user by substituting the value of the wrong product that was actually there! So for most real business problems, approximate search is best not to allow. The exception is when we are looking for numbers and not text – for example, when calculating Step Discounts.

Everything! It remains to press OK and copy the entered function to the entire column.

# N / A errors and their suppression

Function VPR (VLOOKUP) returns #N/A error (#N/A) if a:

  • Exact search enabled (argument Interval view = 0) and the desired name is not in Table.
  • Coarse search included (Interval view = 1), but Table, in which the search is taking place is not sorted in ascending order of names.
  • The format of the cell where the required value of the name comes from (for example, B3 in our case) and the format of the cells of the first column (F3: F19) of the table are different (for example, numeric and text). This case is especially typical when using numeric codes (account numbers, identifiers, dates, etc.) instead of text names. In this case, you can use the functions Ч и TEXT to convert data formats. It will look something like this:

    =VLOOKUP(TEXT(B3),price,0)

    You can read more about this here.

  • The function cannot find the required value because the code contains spaces or invisible non-printable characters (line breaks, etc.). In this case, you can use text functions TRIM (TRIM) и PRINT(CLEAN) to remove them:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),price,0)

    =VLOOKUP(TRIM(CLEAN(B3));price;0)

To suppress the error message #N/A (#N/A) in cases where the function cannot find an exact match, you can use the function IFERROR (IFERROR)… So, for example, this construction intercepts any errors generated by the VLOOKUP and replaces them with zeros:

= IFERROR (VLOOKUP (B3, price, 2, 0), 0)

= IFERROR (VLOOKUP (B3; price; 2; 0); 0)

PS

If you need to extract not one value, but the entire set at once (if there are several different ones), then you will have to shamanize with the array formula. or use the new XLOOKUP feature from Office 365.

 

  • An improved version of the VLOOKUP function (VLOOKUP 2).
  • Quick calculation of step (range) discounts using the VLOOKUP function.
  • How to make a “left VLOOKUP” using the INDEX and MATCH functions
  • How to use the VLOOKUP function to fill in the forms with data from the list
  • How to pull out not the first, but all the values ​​​​from the table at once
  • VLOOKUP2 and VLOOKUP3 functions from the PLEX add-on

 

Leave a Reply