PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Discussion > Checking numbers in range

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Checking numbers in range

 
 
Jan Kronsell
Guest
Posts: n/a
 
      6th Jan 2006
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
 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      6th Jan 2006
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" <kronsell(nomorespam)@adslhome.dk> wrote in message
news:esu$(E-Mail Removed)...
> 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
>
>
 
Reply With Quote
 
Jan Kronsell
Guest
Posts: n/a
 
      6th Jan 2006
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" <(E-Mail Removed)> skrev i en meddelelse
news:(E-Mail Removed)...
> Use COUNTIF, such as
>

>=IF(COUNTIF($D$1:$F$2,A1)>0,A!,"")
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
> news:esu$(E-Mail Removed)...
>> 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
>>
>>
>
>
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      6th Jan 2006
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" <kronsell(nomorespam)@adslhome.dk> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i en meddelelse
> news:(E-Mail Removed)...
> > Use COUNTIF, such as
> >
>
> >=IF(COUNTIF($D$1:$F$2,A1)>0,A!,"")
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
> > news:esu$(E-Mail Removed)...
> >> 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
> >>
> >>
> >
> >
>
>
 
Reply With Quote
 
Jan Kronsell
Guest
Posts: n/a
 
      6th Jan 2006
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" <(E-Mail Removed)> skrev i en meddelelse
news:(E-Mail Removed)...
> 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" <kronsell(nomorespam)@adslhome.dk> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> skrev i en meddelelse
>> news:(E-Mail Removed)...
>> > Use COUNTIF, such as
>> >
>>
>> >=IF(COUNTIF($D$1:$F$2,A1)>0,A!,"")
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from email address if mailing direct)
>> >
>> > "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
>> > news:esu$(E-Mail Removed)...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      6th Jan 2006
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" <kronsell(nomorespam)@adslhome.dk> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i en meddelelse
> news:(E-Mail Removed)...
> > 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" <kronsell(nomorespam)@adslhome.dk> wrote in message
> > news:%(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> skrev i en
meddelelse
> >> news:(E-Mail Removed)...
> >> > Use COUNTIF, such as
> >> >
> >>
> >> >=IF(COUNTIF($D$1:$F$2,A1)>0,A!,"")
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from email address if mailing direct)
> >> >
> >> > "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
> >> > news:esu$(E-Mail Removed)...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
 
Reply With Quote
 
Jan Kronsell
Guest
Posts: n/a
 
      6th Jan 2006
I will try it later.

Jan
"Bob Phillips" <(E-Mail Removed)> skrev i en meddelelse
news:(E-Mail Removed)...
> 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" <kronsell(nomorespam)@adslhome.dk> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> skrev i en meddelelse
>> news:(E-Mail Removed)...
>> > 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" <kronsell(nomorespam)@adslhome.dk> wrote in message
>> > news:%(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> skrev i en
> meddelelse
>> >> news:(E-Mail Removed)...
>> >> > Use COUNTIF, such as
>> >> >
>> >>
>> >> >=IF(COUNTIF($D$1:$F$2,A1)>0,A!,"")
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > (remove nothere from email address if mailing direct)
>> >> >
>> >> > "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
>> >> > news:esu$(E-Mail Removed)...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>
 
Reply With Quote
 
Jan Kronsell
Guest
Posts: n/a
 
      7th Jan 2006
Thank you. I got it to work.

Jan
"Bob Phillips" <(E-Mail Removed)> skrev i en meddelelse
news:(E-Mail Removed)...
> 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" <kronsell(nomorespam)@adslhome.dk> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> skrev i en meddelelse
>> news:(E-Mail Removed)...
>> > 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" <kronsell(nomorespam)@adslhome.dk> wrote in message
>> > news:%(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> skrev i en
> meddelelse
>> >> news:(E-Mail Removed)...
>> >> > Use COUNTIF, such as
>> >> >
>> >>
>> >> >=IF(COUNTIF($D$1:$F$2,A1)>0,A!,"")
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > (remove nothere from email address if mailing direct)
>> >> >
>> >> > "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
>> >> > news:esu$(E-Mail Removed)...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>
 
Reply With Quote
 
Paul Smith
Guest
Posts: n/a
 
      10th Jan 2006
> 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
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      10th Jan 2006
=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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > 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
>
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« format cells | repeat headline that are not connected »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
two columns range of numbers need to list all numbers in the range =?Utf-8?B?YXJzb3ZhdA==?= Microsoft Excel New Users 2 30th Oct 2006 08:21 PM
Checking range of cells for entry then checking for total =?Utf-8?B?QmFyYiBSZWluaGFyZHQ=?= Microsoft Excel Programming 1 13th Oct 2006 02:47 PM
Checking if a cell value in one range is contained in a second range? ModelerGirl Microsoft Excel Discussion 4 21st Feb 2006 03:16 PM
Count comma separated numbers, numbers in a range with dash, not t =?Utf-8?B?TWFoZW5kcmE=?= Microsoft Excel Misc 0 8th Aug 2005 05:56 PM
Excel checking numbers against a large list of numbers =?Utf-8?B?TWF4b24=?= Microsoft Excel Worksheet Functions 2 24th Sep 2004 11:40 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 03:52 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top