Finding a number

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in this
range.
Can any one help, Thanks in advance, Sam.
 
One way...

A1:T1 = numbers

A3 = 39814
B3 = 39844

Is it just a coincidence that those are the serial date numbers for the
dates 1/1/2009 and 1/31/2009 ?

=SUMIF(A1:T1,">="&A3,A1:T1)-SUMIF(A1:T1,">"&B3,A1:T1)
 
=SUMIF(A1:T1,">="&A3,A1:T1)-SUMIF(A1:T1,">"&B3,A1:T1)

Even better!

=SUMIF(A1:T1,">="&A3)-SUMIF(A1:T1,">"&B3)
 
Here's one way, Sam

Assume your source range is A2:A20
Put this in the cell where you want the result,
say in B2, press normal ENTER to confirm:
=INDEX(A2:A20,MATCH(1,INDEX((A2:A20>39814)*(A2:A20<39844),),0))
B2 will return the required result

If you need an error trap to return neat looking blanks should there not be
a match, use IF(ISNA to trap the MATCH part of it, indicatively:
=IF(ISNA(MATCH(..)),"",INDEX(..))

viz, in B2
=IF(ISNA(MATCH(1,INDEX((A2:A20>39814)*(A2:A20<39844),),0)),"",INDEX(A2:A20,MATCH(1,INDEX((A2:A20>39814)*(A2:A20<39844),),0)))

Success? celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
HI, T thanks a will give that a try and yes they are dates, my dad said it
may be easier to convert them to numbers, Sam
 
Back
Top