Например. У меня есть список А и список Б.
Список A включает в себя (1,2,3,4,5) Список B включает в себя (6,2,3,4,0)
Я хочу вывод, которого нет в списке A: 6,0 Без возврата ошибки, как в vlookup.
Заранее спасибо!
2 ответа
1
С данными в столбцах A и B выберите C1- C5 и введите формулу массива:
=IF(ISERROR(MATCH(B1:B5,A1:A5,0)),B1:B5,"")
Формулы массива должны вводиться с помощью Ctrl + Shift + Enter, а не только клавиша Enter .
Обратите внимание на скобки, которые появляются в панели формул.
Ссылка:
0
Очень короткий ответ:
=IFERROR(VLOOKUP(...),"NOT IN LIST")
Более полный ответ:
--- A --- --- B --- --- C --- --- D --- --- E ---
[1] A, database B, lookup val Vlookup() Match() Cell()
[2] 1 6 NOT IN LIST -1 NOT FOUND
[3] 2 2 2 2 $A$3
[4] 3 3 3 3 $A$4
[5] 4 4 4 4 $A$5
[6] 5 0 NOT IN LIST -1 NOT FOUND
Использование IFERROR как в =IFERROR(VLOOKUP(B2;$A$2:$A$6;1;0);"NOT IN LIST") (пример из ячейки C2) будет отображать "NOT IN LIST" для любой ошибки из VLOOKUP ().
Тот же поиск можно выполнить с помощью MATCH(), с той разницей, что возвращаемое значение будет индексом в списке ("1" для первой ячейки); пример в столбце D выше; D2 содержит =IFERROR(MATCH(B2;$A$2:$A$6;0);-1) где последний -1 отображается для ошибки (например, "не найден").
Результат из столбца D может затем обрабатываться для получения адреса ячейки, как в столбце E; E2 содержит =IF(D2>0;CELL("address";OFFSET($A$2;D2-1;0));"NOT FOUND") - если это не NOT FOUND , INDIRECT(D2) вернет значение из ячейки в столбце A (как указано в ссылке).
Если вы предпочитаете иметь список только с искомыми значениями, тогда используйте =IF(ISNA(VLOOKUP(B2;$A$2:$A$6;1;0));B2;"") с использованием VLOOKUP() или =IF(ISNA(MATCH(B2;$A$2:$A$6;0));B2;"") для использования MATCH() - вы должны ввести их в строке 2, как они написаны здесь . Они включены в столбцы F и G в файле CSV ниже.
Скопируйте текст CSV в Notepad (не Wordpad!) или любой другой текстовый редактор. Затем сохраните его в файл, сделайте имя файла оканчивающимся на .csv . Теперь файл должен нормально открываться в вашей электронной таблице (Excel, LibreOffice-Calc, ...) - он должен работать в любой из них.
CSV-файл для вышеуказанного:
"A, database","B, lookup val","'Vlookup()","'Match()","Cell()","'REV Vlookup()","'REV Match()"
1,6,"=IFERROR(VLOOKUP(B2;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B2;$A$2:$A$6;0);-1)","=IF(D2>0;CELL(""address"";OFFSET($A$2;D2-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B2;$A$2:$A$6;1;0));B2;"""")","=IF(ISNA(MATCH(B2;$A$2:$A$6;0));B2;"""")"
2,2,"=IFERROR(VLOOKUP(B3;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B3;$A$2:$A$6;0);-1)","=IF(D3>0;CELL(""address"";OFFSET($A$2;D3-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B3;$A$2:$A$6;1;0));B3;"""")","=IF(ISNA(MATCH(B3;$A$2:$A$6;0));B3;"""")"
3,3,"=IFERROR(VLOOKUP(B4;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B4;$A$2:$A$6;0);-1)","=IF(D4>0;CELL(""address"";OFFSET($A$2;D4-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B4;$A$2:$A$6;1;0));B4;"""")","=IF(ISNA(MATCH(B4;$A$2:$A$6;0));B4;"""")"
4,4,"=IFERROR(VLOOKUP(B5;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B5;$A$2:$A$6;0);-1)","=IF(D5>0;CELL(""address"";OFFSET($A$2;D5-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B5;$A$2:$A$6;1;0));B5;"""")","=IF(ISNA(MATCH(B5;$A$2:$A$6;0));B5;"""")"
5,0,"=IFERROR(VLOOKUP(B6;$A$2:$A$6;1;0);""NOT IN LIST"")","=IFERROR(MATCH(B6;$A$2:$A$6;0);-1)","=IF(D6>0;CELL(""address"";OFFSET($A$2;D6-1;0));""NOT FOUND"")","=IF(ISNA(VLOOKUP(B6;$A$2:$A$6;1;0));B6;"""")","=IF(ISNA(MATCH(B6;$A$2:$A$6;0));B6;"""")"