Artificial Intelligence Question posted in
ChatGBT is becoming a world-wide phenomena, try it out here.

Excel 365 Worksheet Array-Formula to Return Values NOT Found? – Artificial Intelligence

What array formula will return values which don’t appear in another list?

Example:
Cells named ShortList contain (one word per cell):

How recent  neuroscience    research    points  the way towards defeating   adversarial examples    and achieving   a   more    resilient   consistent  and flexible    form    of  artificial  intelligence

And cells named LongList contain:

a   about   above   across  and how however i   instead more    neither never   nevertheless    next    no  of  second  several shall   she the towards

Here’s the expected output of the formula:

recent  neuroscience    research    points  way defeating   adversarial examples    achieving   resilient   consistent  flexible    form    artificial  intelligence

I’m thinking it might involve, match, xmatch, lookup, or xlookup.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    i think i got it. This returns the expected result in my question.

    =FILTER(ShortList, ISNA(MATCH(ShortList, LongList, 0)))

    hoping to see alternate answers.


  2. 0

    When your FILTER() formula works fine but for large dataset LET() may give you twice faster performance as per Microsoft Documentation

    =LET(x,A1:A3,y,ISNUMBER(MATCH(x,B1:B10,0)),FILTER(x,y=FALSE))

    With name manager try-

    =LET(x,ShortList,y,ISNUMBER(MATCH(x,LongList,0)),FILTER(x,y=FALSE))

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search