List 'values' that are not found in the range via formula

[dluhut]

Hello,

Say in Workbook 1, I have the following list

a

b

c

d

e

In Workbook 2, I have the following list

a	description of a
c	description of c
d	description of d
e	description of e
g	description of g
h	description of h
i	description of i
j	description of j
k	description of k
l	description of l

How can I in Workbook 1, return the list of range that are not found?

In this case, it should listed out g to l

[mehmetcik]

Use the match function to find matching cells.




Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dluhut]

Have attached.

My question is how to list out those that are not 'match'

[JohnTopley]

In Workbook 2

In "helper" column (J in attached)

=COUNTIFS($A$2:$A$6,G2)

in Workbook 1

Column D

=IFERROR(INDEX($G$2:$G$12,SMALL(IF($J$2:$J$12=0,ROW($G$2:$G$12)-ROW($G$2)+1,""),ROWS($G$2:G2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[dluhut]

Hi JohnTopley,

I can't modify the Workbook 2, thus that's the reason why I am using formula to check what's missing between Workbook 1 and Workbook 2.

By any chance the 'helper' column is 'embedded' in Workbook 1?

Edit:

Solved!

Was able to incorporate what you've done to Workbook 2 to Workbook 1 with ISNUMBER and MATCH formula.

Reps up to you for the formula that I need which is to list.

[JohnTopley]

No: you need to compare the "long" list (Workbook 2) with the "short" list (Workbook 1") to identify differences i.e does 2 exist in 1.

[JohnTopley]

See attached:

in Workbook 1 (Column F)

=COUNTIFS($A$2:$A$6,H2)

in E2

=IFERROR(INDEX($H$2:$H$12,SMALL(IF($F$2:$F$12=0,ROW($H$2:$H$12)-ROW($H$2)+1,""),ROWS($H$2:I2))),"")



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.