Matching Data within Cells

[Steve Boyle]

First I am a newbie to this community but I am by no means a newbie to
Excel. I have a need where I work to match two different columns of data as
follows. Anyone that can help will be my savior.

Problem to solve:
Cell A2 contains a 16 digit number stored as text (Account Number)
This is one of several such cells in column A
Column E contains a list of Account numbers from another report that has
been copied to the work sheet

Solution required:
search Column E for a match to Cell A2 and indicate that there is a
match in some manner

If possible could more than one cell be compared to more than one column
with a match having to be all or none.

Thanks in advance for your help.

Steve Boyle

 

[Tyro]

Assuming your column E entries are in E1:E100 then =COUNTIF(E1:E100,A2) will
tell you how many times A2 matches the entries in E2:E100

If your values are in C1:E10 and the values you want to count are in A1:A5
then the array formula
=SUM(COUNTIF(C1:E10,INDIRECT("A1:A5"))) (Press Ctrl+Shift+Enter)

That formula will sum the number of times each entry in A1:A5 occurs in
C1:E10.

I'm not sure what you mean by "all or none"

Tyro

 

[scott]

Assuming you have a header row
A(Account#) | B...| C...| D...| E(Lookups)| F(found)

if The account numbers are in range A2:A5000
and what you are searching for is in E2:E5000
F could have this formula, Insert this in F2 and drag it down as
needed

=IF(ISNA(VLOOKUP(E2,$A$2:$A$5000,1,FALSE)),"n","y")

 

[Steve Boyle]

Tyro:
All or None means if comparing 3 items All must match for a match but if
only one or two match it is not a complete match.

Does that explain it?

Steve

 

[Tyro]

This array formula returns TRUE if any of the values in A1:A5 does not
appear in C1:E10 and FALSE if all appear

=IF(OR(COUNTIF(C1:E10,INDIRECT("A1:A5"))=0),TRUE,FALSE) (Ctrl+Shift+Enter)

Tyro

 

[Tyro]

As a follow-up, the array formulas treat blanks as equal to 0's, but in your
case with the 16 digit account numbers that may not be an issue.

Tyro

 

[scott]

It is not clear to me. Are there ONLY account numbers in column A?
Scott