In Excel 2 sheets. The first is Name, Date of Birth. And the second is the Name, the Name of the school. It is necessary to insert in the second sheet the year of birth from the first sheet by comparing the names of the students. Tell me, how can this be done?

2 answers 2

One solution is to use the ВПР() function. 1. We have a sheet Имя-ДтРождения with columns Имя , Дата рождения . Each name has a date of birth. Mandatory conditions for the correct operation of the ВПР() function ВПР() names must be sorted alphabetically, the name must be unique.

Sheet "Name-Relay"

  1. We have a sheet Имя-Школа with columns Имя , Школа , Дата рождения . Values ​​for Имя , Школа columns are defined.

Sheet "Name-School"

  1. To search for the Имени the date of birth from the Имя-ДтРождения use the ВПР() function ВПР() . For cell С2 value of the function is: =ВПР(A2;'Имя-ДтРождения'!$A$2:$B$11;2;ЛОЖЬ)

The value of the function CDF () for the cell "C2"

    In addition to the previous answer

    names must be sorted alphabetically

    No, not necessarily. The interval-look function FALSE (0) searches for an exact match of the value.

    Sorting is needed if interval viewing is TRUE (1) . This is applicable in cases where, for example, it is necessary to determine the value belonging to a range.

    Example of searching by inaccurate matching: https://ru.stackoverflow.com/questions/375079/Formula-excel-or-as- select- groups- values/375087 #375087

    If the desired value is to the right of the extracted one, the CDF in the normal record will not cope. In this case, you can use the INDEX / MATCH pair :

     =ИНДЕКС(столбец_извлечения;ПОИСКПОЗ(искомое;столбец_поиска;))