Pull up the price when the product name matches

This is easily done using the CDF function.

Here is the dialog box for this function:

How to use the CDF function.

=ВПР(B2;C2:E7,3,ИСТИНА) 

The first argument (the part of the data that provides the function) is the desired value. This may be a reference to a cell or a specific value, for example, “Yellow pencil” or “Laser printer”. The second argument is a range of cells, which, in your opinion, contains the desired value. In our example, this is the C2 – C7 range. The third argument is a column in the range of cells that contains the value to be found.

The fourth argument is optional. You can enter TRUE or FALSE. If you enter TRUE or leave the argument empty, the function returns an approximate match of the value specified as the first argument. If you enter FALSE, the function will match the value specified as the first argument. In other words, to provide additional flexibility, leave the fourth argument empty or enter the value TRUE.

This example shows how the function works. If you enter a value in cell B2 (first argument), the CDF function searches in cells C2 – E7 (second argument) and returns the most accurate approximate match from the third column in the range — column E (third argument).

The fourth argument is empty, so the function returns an approximate match. Otherwise, you will need to enter one of the values ​​in column C or D to get any result.

For details, see the Quick Reference: VLR function.

And also - Search for values ​​using the functions of the CDF

To solve the task, link the name of the Goods on Листе 2 with the Ценой on Листе 1 , use the MS Excel function ВПР() .

1. Create Лист 1 (Product Database) with a list of products ( Товар column) and price ( Цена column). We fill the cells with the values ​​of Products and Prices.

Mandatory and necessary conditions for the correct operation of the ВПР() function ВПР() are: the Товар column is the first in the list and the values ​​are sorted in alphabetical order.

Restrictions: correctly the ВПР() function ВПР() will not work for the same goods with different prices. For example:

Tea 122.00;

Tea 170.00.

2. Create a Лист 2 (Pry-list) with columns Товар and Цена .

3. We associate the value of the imported goods with the prices placed on Листе 1 using the ВПР() function ВПР() through the name of the goods.

The ВПР() function is called as follows:

• the first argument is the Искомое_значение that we are looking for in Листе 1 . In our case, А4 ;

• the second argument is the Таблица , where we search, on which sheet and in what range. In our case, Лист1!$A$2:$B$9 . Pay attention to the absolute links when setting the range $A$2:$B$9 to avoid changing the search range when copying cells with formulas;

• the third argument is the column_number, the column number from which the value will be returned in case of a desired search value. In our case 2 . If a value is found, then the value of the cell of the 2nd column will be returned, if not, then the value is #Н/Д ;

• the fourth argument is Interval_View, a logical value that determines the accuracy of the search by the desired_value. ЛОЖЬ - exact match, ИСТИНА - approximate.

4. On Листе 2 replicate the cell with the ВПР() formula ВПР() in the Цена column below. When you enter a value in the Товар column, if the name matches the product value on Листе 1 , the ВПР() function ВПР() returns the price value from the Цена column or #Н/Д value.

The result is a minimum achieved.

5. To hide the #Н/Д value in the absence of goods that have yet been entered and display a notification about the absence of goods on Листе 1 supplement the ВПР() function ВПР() following conditions and functions: ЕСЛИ() , ЕНД() , ЕПУСТО() . The final edition of the function: =ЕСЛИ(ЕПУСТО(A4);"";ЕСЛИ(ЕНД(ВПР(A4;Лист1!$A$2:$B$9;2;ЛОЖЬ));"Нет продукта в списке цен";ВПР(A4;Лист1!$A$2:$B$9;2;ЛОЖЬ)))