Checking numbers in range

[Jan Kronsell]

Hi NG

I have two ranges. The first is a column of numbers say A:A), maybe

1
2
3
4
5

The other range is just a range of numbers (say C1:E2).

2 9 7
6 10 8

I now want to test if one of the numbers in range 1 exists in range 2 (range
2 contians either one, og no numbers from range 1, never more than one.). If
a number exist, I want to return the value of that number, if it does not, i
want to return "".

Jan

 

[Bob Phillips]

Use COUNTIF, such as

=IF(COUNTIF($D$1:$F$2,A1)>0,A1,"")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jan Kronsell]

Maybe I'm doing something wrong, but in the case it only checks with the
number i A1 I think. so if the number exist in any other cell in range one,
it always returns a zero. What I really need is to compare each cell in
range 1, with each cell in range 2 in one formula. Something like

=IF(COUNTIF($D$1:$F$2,A:A)>0,value from found cell in A:A,"")

but that doesn't work even if I try to make it an arrayformula.

Jan

 

[Bob Phillips]

But you want to know what number matches, so if there is more than one,
which should it return?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jan Kronsell]

There is never more than one. Either one or none. So if there is none, it
should return none. If thetre is one, it should return that one.

Jan

 

[Bob Phillips]

Ok Jan, try this

=INDEX(A1:A5,SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jan Kronsell]

I will try it later.

Jan

Ok Jan, try this

=INDEX(A1:A5,SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jan Kronsell]

Thank you. I got it to work.

Jan

Ok Jan, try this

=INDEX(A1:A5,SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Paul Smith]

Ok Jan, try this

=INDEX(A1:A5,SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5)))

The formula above does not produce, as it was supposed, the result ""
if there is no common elements.

Paul

 

[Bob Phillips]

=IF(SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))=0,"",INDEX(A1:A5,SUMPROD
UCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Paul Smith]

=IF(SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))=0,"",INDEX(A1:A5,SUMPROD

UCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))))

Thanks, Bob. Your new formula works fine. Can the same problem be
solved if, instead of a column (A1:A5), one has a bi-dimensional range
(for instance, A1:B5)?

Paul

 

[Bob Phillips]

Yeah, but it's getting messy

=IF(AND(SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))=0,SUMPRODUCT(--COUNT
IF(E1:G2,B1:B5),--ROW(B1:B5))=0),"",
IF(SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))<>0,INDEX(A1:A5,SUMPRODUCT
(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))),INDEX(B1:B5,SUMPRODUCT(--COUNTIF(E1:G
2,B1:B5),--ROW(B1:B5)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Harlan Grove]

Bob Phillips wrote...

=IF(SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))=0,
"",INDEX(A1:A5,SUMPRODUCT(--COUNTIF(E1:G2,A1:A5),--ROW(A1:A5))))

....

The double unary minuses are unnecessary.

Using the OP's original ranges, just to be difficult, an alternative:

=CHOOSE(1+MIN(SUMPRODUCT(COUNTIF(C1:E2,A1:A5)),2),"",
SUMPRODUCT(SUMIF(C1:E2,A1:A5)),"impossible!")

Note that it's also sensible to trap "impossible" situations just in
case they do happen to be possible.

 

[Bob Phillips]

Paul,

Harlan is correct of course, the double unary is superfluous which makes my
formula somewhat simpler

=IF(AND(SUMPRODUCT(COUNTIF(E1:G2,A1:A5),ROW(A1:A5))=0,SUMPRODUCT(COUNTIF(E1:
G2,B1:B5),ROW(B1:B5))=0),"",
IF(SUMPRODUCT(COUNTIF(E1:G2,A1:A5),ROW(A1:A5))<>0,INDEX(A1:A5,SUMPRODUCT(COU
NTIF(E1:G2,A1:A5),ROW(A1:A5))),
INDEX(B1:B5,SUMPRODUCT(COUNTIF(E1:G2,B1:B5),ROW(B1:B5)))))

but we can do better by extending Harlan's elegant little single column
formula to 2 columns with

=CHOOSE(1+MIN(SUMPRODUCT(COUNTIF(E1:G2,A1:B5)),2),"",
SUMPRODUCT(SUMIF(E1:G2,A1:B5)),"impossible!")

or even better as we can now trap double entries in A1:B5, something the OP
said wouldn't happen but concerned me

=CHOOSE(1+MIN(SUMPRODUCT(COUNTIF(E1:G2,A1:B5)),2),"",
SUMPRODUCT(SUMIF(E1:G2,A1:B5)),"More than one result possible")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Paul Smith]

=CHOOSE(1+MIN(SUMPRODUCT(COUNTIF(E1:G2,A1:B5)),2),"",

SUMPRODUCT(SUMIF(E1:G2,A1:B5)),"More than one result possible")

Thanks, Bob and Harlan. What an elegant solution!

Paul