В Excel 2011 на Mac, как вернуть непустое значение соответствия с vlookup?

Question

У меня есть список доменных имен и их регистраторов на одном листе, и тот же список доменов и других данных (но не регистраторов) на другом листе.

Мне нужно получить имя регистратора на листе A, чтобы быть рядом с соответствующим доменным именем на листе B.

CSV пример листа А:

domain1.com,,9.98,03/24/13
domain1.com,,9.98,03/24/13
domain1.com,godaddy,9.98,03/24/13
domain2.com,,9.98,03/24/13
domain2.com,godaddy,9.98,03/24/13
domain3.com,namecheap,9.98,03/24/13

CSV пример листа B:

domain1.com,1200,04/01/14
domain2.com,402,04/01/14
domain3.com,612,04/01/14

Я желаю иметь регистратора в 4-м столбце листа B для каждого домена.

Проблема состоит в том, что на листе A есть несколько экземпляров домена, где он был с другими регистраторами в прошлом, но эти ячейки были стерты. Это приводит к тому, что мой vlookup из листа B находит первое совпадение с именем домена и возвращает соответствующего регистратора, который в итоге остается пустым (возвращает ноль).

Есть ли способ заставить VLookup (или другую формулу) вернуть только соответствующий регистратор, который НЕ пуст?

Я немного поэкспериментировал с Index & Match, но не смог приблизиться.

Answer 1

Вы можете использовать формулу массива для этого поиска. Если ваши исходные данные представлены в A1:D6 , вы можете использовать эту формулу (отрегулируйте ссылки на листы в соответствии с вашей рабочей книгой):

=INDEX(SheetA!$B$1:$B$6,MIN(IF(SheetA!$A$1:$A$6=SheetB!A1,IF(SheetA!$B$1:$B$6="",2000000,ROW(SheetA!$B$1:$B$6)),2000000)))

Вставьте это в строку формул и нажмите Ctrl+Shift+Enter. Тогда заполните.

Объяснения и предположения:

• Операторы IF внутри функции MIN образуют массив числовых значений. Для записей, которые соответствуют критериям (т. Е. Доменное имя соответствует, а регистратор не пуст), номер строки записи сохраняется в массиве. Для записей, которые не соответствуют критериям, число 2000000 сохраняется в массиве.
• 2000000 - это произвольное число, превышающее количество строк в листе Excel. Это просто для гарантии того, что если совпадений не найдено, функция INDEX вернет ошибку.
• INDEX возвращает значение в указанной позиции в массиве.
• Номер строки записи используется в качестве замены для позиции записей в таблице. Это хорошо, если таблица начинается в строке 1, потому что запись 1 находится в строке 1. Однако, если ваши данные начинаются в другой строке, вам нужно настроить формулу, чтобы эта работа работала. Например, если запись 1 находится в строке 2, вам нужно будет заменить ROW($B$2:$B$7) на ROW($B$2:$B$7)-1 . В противном случае функция INDEX вернет неправильный регистратор.