On Feb 8, 2:45*pm, "T. Valko" wrote:
wrote in message

...





Hi,

Lets say I have data in a range A1:A5 that contains
"A","B","C","D","E" in each cell respectively. *In Range B1:B3, *the
values are "A","D","E".

I want to be able to extract the *"B" and "D" using array formula.

I created a formula * {=MATCH(A1:A5,B1:B3,0)} which creates a list of
numbers (index value of the location of the matches in the list) where
there are matches and #N/A where there are no matches. *I want to be
able to pull out the #N/A index location so that I can identify the
"B" and then the "D", perhaps using a Small function but I have gotten
stuck....

Please advise.

TIA

I want to be able to extract the *"B" and "D"

Shouldn't that be "B" and "C"?

Enter this array formula in D1 and copy down until you get #NUM! errors
meaning all data has been extracted:

=INDEX(A$1:A$5,SMALL(IF(COUNTIF(B$1:B$3,A$1:A$5)=0 ,ROW(A$1:A$5)-MIN(ROW(A$1*:A$5))+1),ROWS(D$1:D1)))

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Thanks Biff - this is the type of solution I was looking for - solves
the base problem exactly how I envisioned.

Do you have a way to prevent the return of duplicates in Column A -
say you had "A","B","C","D","E","A","B" -

Is there a way we could ad a countif to check the solution set (those
cells that are in the range from the first row of the solution to the
row above the current row as well as check B1:B3 (criteria range)?

Thanks again.