Сравните столбцы из разных файлов Excel и, если совпадение, добавьте к последнему столбцу (в той же строке) статус

Question

Я хочу начать с того, что я не эксперт по Excel, поэтому мне нужна помощь.

Давайте предположим, что у меня есть 3 файла Excel: main.xlsx , 1.xlsx и 2.xlsx . Во всех них у меня есть колонка с именем Serial Numbers . Мне пришлось:

• найдите все серийные номера в 1.xlsx и 2.xlsx и убедитесь, что они есть в main.xlsx .

Если серийный номер найден:

• в последнем столбце main.xlsx , в той же строке с серийным номером, который был найден, напишите OK + name_of_the_file_in which_it_was_found . Остальное напиши NOK . В то же время напишите в последнем столбце 1.xlsx и 2.xlsx ok или nok если был найден серийный номер или нет.

Упоминание: serial number может быть в разных столбцах на 1.xlsx и 2.xlsx

Пример:

main.xlsx

name date serial number phone status
a      b      abcd        c         <-- ok,2.xlsx
b      c      1234        d         <-- ok,1.xlsx
c      d      3456        e         <-- ok,1.xlsx
d      e      4567        f         <-- NOK
e      f                  g         <-- skip,don't write anything to status column

1.xlsx

name date serial number phone status
a      b      1234        c          <-- OK (because is find in main)
b      c      lala        d          <-- NOK (because not find in main)
c      d      3456        e          <-- OK (because find main)
d      e      jjjj        f          <-- NOK (because not find in main)
e      f                  g          <-- skip,don't write anything to status column

2.xlsx

name date serial number phone status
a      b                  c          <-- skip,don't write anything to status column
b      c      abcd        d          <-- OK (because find main)
c      d      4533        e          <-- NOK (because not find in main)
d      e      jjjj        f          <-- NOK (because not find in main)
e      f                  g          <-- skip,don't write anything to status column

Я знаю, что могу использовать VLOOKUP или MATCH но я совершенно незнаком с формулами Excel.

Answer 1

Мы будем использовать комбинацию следующих функций:

• ISBLANK чтобы проверить, имеет ли ячейка значение, в противном случае мы пропустим его, и результирующий статус будет пустым.
• VLOOKUP чтобы найти значение в другой таблице в другом файле. Мы могли бы использовать функцию LOOKUP но у нее есть свои проблемы, и VLOOKUP позволяет нам указать, что мы хотим точное совпадение.
• ISERROR чтобы проверить, нашел ли VLOOKUP совпадение или вернул ошибку.

В столбце состояния main.xlsx используйте следующую формулу в первой строке и примените к остальным строкам под ней:

=IF(ISBLANK(C2),"",IF(ISERROR(VLOOKUP(C2,[1.xlsx]Sheet1!$C:$C,1,FALSE)),IF(ISERROR(VLOOKUP(C2,[2.xlsx]Sheet1!$C:$C,1,FALSE)),"NOK","ok,2.xslx"),"ok,1.xslx"))

В столбцах состояния 1.xslx и 2.xlsx используйте эту формулу в первой строке и примените к остальным строкам под ней:

=IF(ISBLANK(C2),"",IF(ISERROR(VLOOKUP(C2,[Main.xlsx]Sheet1!$C:$C,1,FALSE)),"NOK","OK"))

Примечание. Формулы относятся к столбцу C, поскольку на основе приведенных вами примеров здесь находится серийный номер.