В качестве темы я выполняю ВПР между двумя файлами Excel. Формула возвращает значение from reference_row-1(«Room») вместо reference_row(DPS DPC). Почему это произошло и как это исправить?
решение1
Комментарий пользователя Mark Fitzgeraldнаходится на правильном пути, но не совсем туда.
Правильная часть заключается в том, что четвертый параметр не указан И не указан как ЛОЖЬ.
Если не указано, то по умолчанию это TRUE. Это также TRUE, если указано как TRUE. В любом случае, вы сталкиваетесь с проблемой, которую видите здесь, но здесь есть что-то еще, что вызывает именно тот несчастливый результат, который вы получаете.
Прежде чем заняться этим, я должен отметить, что в вашем случае вам явно нужно точное совпадение для корректных результатов. Поэтому вы должны добавить четвертый параметр и сделать его ЛОЖНЫМ, чтобы он искал точное совпадение. Тогда основная проблема, которую я собираюсь решить, не будет иметь значения, что является преимуществом.
Настоящая проблема возникает из того, как вы сообщаете Excel, какой диапазон использовать для поиска: второй параметр. Вы используете, C:Vкоторый говорит Excel использовать каждую отдельную ячейку в этих столбцах. Есть КУЧА причин НЕ делать этого, но та, которая имеет значение здесь, связана со значением TRUE для четвертого параметра.
Когда это TRUE, Microsoft сообщает нам VLOOKUP()(и некоторым другим функциям, если мы ищем что-то, не ожидая точного совпадения), что будет искать по данным, пока не найдет ячейку, которая "больше" искомого значения, затем остановится и вернет значение ПРЯМО ПЕРЕД этим значением "больше". Я упомяну, что это не совсем правда, но достаточно близко для вашей сложности.
Из-за того, что они утверждают, они предполагают, что правильные результаты будут получены только тогда, когда столбец поиска (здесь столбец C) отсортирован от наименьшего значения к наибольшему («AZ»). И это будет работать, почти всегда, даже в вашем случае, вероятно (но с вероятностью 99,99%). К сожалению, огромное количество людей просто не могут сделать такую сортировку, не испортив какую-то другую функциональность в своих данных. Для большинства они просто хотят, чтобы они были отсортированы в соответствии с другими условиями, и не могут сортировать по столбцу поиска.
Итак, что с вами происходит? Поиск ищет "CA-whatever" в столбце C. Но... он начинается с заголовка столбца в ячейке C1. Этот заголовок - "Barcode"... и пока все (вроде) хорошо. Затем в ячейке C2 он находит что-то "большее, чем" искомое значение (которое начинается с "CA"). Допустим, если он находит в ячейке C2 что-то по алфавиту после него и заголовка. Он немедленно останавливается и "отступает" обратно в ячейку заголовка. Это строка 1 в диапазоне, и он читает вправо в столбец S и находит "Room" в этой ячейке (S1). Вот что он возвращает.
По правде говоря, если бы C2 не прошел значение поиска по алфавиту, он бы наверняка нашел что-то, что было задолго до достижения C380, и остановился бы на этой более ранней точке, отступил бы на одну строку и вернул бы значение столбца S этой строки. Что ТАКЖЕ было бы неправильно, но это могло бы быть не настолько очевидно, чтобы вы это заметили. В конце концов, это дало бы некоторое нормально выглядящее Room. Все могло бы выглядеть хорошо. И все же, легко могло бы быть 4 или 32, или даже 200-300 ошибочных значений ПРЯМО СЕЙЧАС.
Как я уже упоминал в начале, вам в любом случае явно нужны точные совпадения, поэтому добавьте четвертый параметр в поиск. Используйте FALSE, а не TRUE. FALSE заставит его искать точное совпадение.
Поскольку проблема была опубликована более двух лет назад, похоже, вы решили ее тем или иным способом. Но, возможно, это будет полезно кому-то еще, кому-то, кто может найти это полезным даже для объяснения сложения результатов с другими функциями, которые предположительно работают так, как говорит MS, но на самом деле работают так, как я сказал, хотя мое затрагивание было лишь небольшой частью всей темы! Но любая функция, которая выполняет поиск, может столкнуться с этим. MATCH()и XMATCH()даже новая XLOOKUP()функция. По общему признанию, более новые функции сталкиваются с этим только при использовании неточного соответствия, но поскольку они это делают, и люди используют их таким образом... И есть другие случаи, когда это возникает.