The LOOKUP formula returns an invalid value (Google Sheets)

There is a source table, there are columns

id | приз | место | статус пользователя

In this source table, the id can be duplicated.

There is a final table

id  | статус пользователя

There ID is already without repetitions. I imported them from the source table like this

=UNIQUE(IMPORTRANGE("ключ";"ПОБЕДИТЕЛИ (ВСЕ)!B2:B"))

Now you need to pull the user's status from the source table to the final one. To do this, I use

=LOOKUP(C2; 'ПОБЕДИТЕЛИ (ВСЕ)'!B$2:B; 'ПОБЕДИТЕЛИ (ВСЕ)'!G$2:G)

Where:

  • C2 is the column number in the final table with the ID
  • ' WINNERS (ALL)'!B$2:B is the search range by ID from the source table
  • ' WINNERS (ALL)'!G$2:G is the range where the response should be taken from the source table (user status)

The values are returned, but when manually checking on the source table, it turns out that they do not match the ID. I.e. if the source page had

    ID  | статус пользователя
   1000 | активен

Then in the final one appears:

  ID  | статус пользователя
 1000 | без статуса

From which we can conclude that LOOKUP returns an invalid value. But I can't figure out why.

Author: Vel Green, 2019-10-30

1 answers

You are not applying the functions correctly.

IMPORTRANGE - to extract data from another Google table. If the tables are located on adjacent sheets, the function is superfluous:

=UNIQUE('Исходник'!B2:B)

LOOKUP (ПРОСМОТР) - the search must be performed in a sorted range. You have all the ID mixed up. Therefore:

- - - or search in a different way:

=LOOKUP(2, 1/('Исходник'!B$2:B=A2), 'Исходник'!E$2:E)

- - - or apply the function VLOOKUP (ВПР)

=VLOOKUP(A2, 'Исходник'!B$2:E, 4,0)

The essential difference between these solutions: LOOKUP (ПРОСМОТР) will find the last row with the desired value, VLOOKUP (ВПР) - the first occurrence.

 0
Author: vikttur, 2019-10-30 14:48:36