Forum Discussion

ExcelLearner2395's avatar
ExcelLearner2395
Copper Contributor
May 27, 2021

Finding data not on a list

Hi guys,   I'm using Windows 10 and Office 365.   I'm looking to try and find all the date that is not on a different list. I have 2 lists of names, 1 which is split into First and Surname (say c...
office 365
training
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 27, 2021

ExcelLearner2395 

As variant

with

=LET(
  range,      A2:C10,
  first,      INDEX(range,0,1),
  last,       INDEX(range,0,2),
  both,       INDEX(range,0,3),
  included,   COUNTIFS(both, first & " " & last),
  bothMissed, FILTER(both, NOT(included)*(both<>"")),
  seps,       FIND(" ",bothMissed),
  firstMissed,LEFT(bothMissed,seps),
  lastMissed, RIGHT(bothMissed,LEN(bothMissed)-seps),
  IF({1,0}, firstMissed, lastMissed)
)

Resources