Search Range for Criteria in given cell and produce results

I have a spreadsheet that has two worksheet. Worksheet "start" has two
columns - Column A is a date Column B is a number - rows contain dates and
continuous numbers EX Row 1 = 0000417 Row 2 = 0000418 Row 3 = 0000419 -
each with a date of 060105.

Worksheet "clear" has two columns - basically the same as Worksheet "start"
- difference is they are not in sequence and not always the same numbers and
they are scrambled. I don't want to sort this data.
I would like to use Worksheet "Clear" - Take the first Row and determine
if the first Number is on Worksheet "start" - if it is then tell me what the
date is from Worksheet "start" - if not then leave it blank and repeat this
for all given numbers.

I am unable to get the RANGE to work for each individual cell on Worksheet
"clear". It works cell by cell but not by range.

EX of formula tested
IF((CLEAR!A1=START!A1:A25),START!B1,"NONE")

The results show {TRUE;FALSE;FALSE....
So that the last value is the only thing that appears in this cell -
therefore getting a NONE result because the last result is FALSE... once the
value is met - I'd like to search the rest of the cells to pull each date for
each item...

IF Clear has dates in Column A and the numbers you want to match in Column
B, then enter this formula in C1 of Clear:

=IF(ISNA(MATCH(B1,Start!$B$1:$B$100,0)),"",INDEX(Start!$A$1:$A$100,MATCH(B1,
Start!$B$1:$B$100,0)))

And copy down as needed.

Make sure the numbers on both sheets are the same type of data.
Those leading zeroes could mean your numbers might be text in one of the
sheets and not in the other.
Also, your dates look questionable.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"RFreeman12" <RFreeman12@discussions.microsoft.com> wrote in message
news:FEB32457-17BD-4A31-BE3B-F86C2A03E72F@microsoft.com...
> I have a spreadsheet that has two worksheet. Worksheet "start" has two
> columns - Column A is a date Column B is a number - rows contain dates
and
> continuous numbers EX Row 1 = 0000417 Row 2 = 0000418 Row 3 =
000419 -
> each with a date of 060105.
>
> Worksheet "clear" has two columns - basically the same as Worksheet
"start"
> - difference is they are not in sequence and not always the same numbers
and
> they are scrambled. I don't want to sort this data.
> I would like to use Worksheet "Clear" - Take the first Row and determine
> if the first Number is on Worksheet "start" - if it is then tell me what
the
> date is from Worksheet "start" - if not then leave it blank and repeat
this
> for all given numbers.
>
> I am unable to get the RANGE to work for each individual cell on Worksheet
> "clear". It works cell by cell but not by range.
>
> EX of formula tested
> IF((CLEAR!A1=START!A1:A25),START!B1,"NONE")
>
> The results show {TRUE;FALSE;FALSE....
> So that the last value is the only thing that appears in this cell -
> therefore getting a NONE result because the last result is FALSE... once
the
> value is met - I'd like to search the rest of the cells to pull each date
for
> each item...
>

OK - the MATCH was exactly what I needed. I actually used it to qualify two
scenarios for the CK# as well as the $$ amount to make sure it was matching
Check for Check. Then if it did not pass both - told it to Research CK #.

Thank you for your help - this was great and saved me time on research.

=IF(ISNA(MATCH(C1,PPCKS!$C$1:$C$100,0)*(ISNA(MATCH(B1,PPCKS!$B$1:$B$100,0)))),"RESEARCH CK#",INDEX(PPCKS!$D$1:D$100,MATCH(B1,PPCKS!$B$1:$B$100,0)))

"RagDyer" wrote:

> IF Clear has dates in Column A and the numbers you want to match in Column
> B, then enter this formula in C1 of Clear:
>
> =IF(ISNA(MATCH(B1,Start!$B$1:$B$100,0)),"",INDEX(Start!$A$1:$A$100,MATCH(B1,
> Start!$B$1:$B$100,0)))
>
> And copy down as needed.
>
> Make sure the numbers on both sheets are the same type of data.
> Those leading zeroes could mean your numbers might be text in one of the
> sheets and not in the other.
> Also, your dates look questionable.
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
>
> "RFreeman12" <RFreeman12@discussions.microsoft.com> wrote in message
> news:FEB32457-17BD-4A31-BE3B-F86C2A03E72F@microsoft.com...
> > I have a spreadsheet that has two worksheet. Worksheet "start" has two
> > columns - Column A is a date Column B is a number - rows contain dates
> and
> > continuous numbers EX Row 1 = 0000417 Row 2 = 0000418 Row 3 =
> 000419 -
> > each with a date of 060105.
> >
> > Worksheet "clear" has two columns - basically the same as Worksheet
> "start"
> > - difference is they are not in sequence and not always the same numbers
> and
> > they are scrambled. I don't want to sort this data.
> > I would like to use Worksheet "Clear" - Take the first Row and determine
> > if the first Number is on Worksheet "start" - if it is then tell me what
> the
> > date is from Worksheet "start" - if not then leave it blank and repeat
> this
> > for all given numbers.
> >
> > I am unable to get the RANGE to work for each individual cell on Worksheet
> > "clear". It works cell by cell but not by range.
> >
> > EX of formula tested
> > IF((CLEAR!A1=START!A1:A25),START!B1,"NONE")
> >
> > The results show {TRUE;FALSE;FALSE....
> > So that the last value is the only thing that appears in this cell -
> > therefore getting a NONE result because the last result is FALSE... once
> the
> > value is met - I'd like to search the rest of the cells to pull each date
> for
> > each item...
> >
>
>

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RFreeman12" <RFreeman12@discussions.microsoft.com> wrote in message
news:6513C03E-CB5C-4DEB-8C61-8A5E8124337D@microsoft.com...
> OK - the MATCH was exactly what I needed. I actually used it to qualify
two
> scenarios for the CK# as well as the $$ amount to make sure it was
matching
> Check for Check. Then if it did not pass both - told it to Research CK #.
>
> Thank you for your help - this was great and saved me time on research.
>
>
=IF(ISNA(MATCH(C1,PPCKS!$C$1:$C$100,0)*(ISNA(MATCH(B1,PPCKS!$B$1:$B$100,0)))
),"RESEARCH CK#",INDEX(PPCKS!$D$1:D$100,MATCH(B1,PPCKS!$B$1:$B$100,0)))
>
> "RagDyer" wrote:
>
> > IF Clear has dates in Column A and the numbers you want to match in
Column
> > B, then enter this formula in C1 of Clear:
> >
> >
=IF(ISNA(MATCH(B1,Start!$B$1:$B$100,0)),"",INDEX(Start!$A$1:$A$100,MATCH(B1,
> > Start!$B$1:$B$100,0)))
> >
> > And copy down as needed.
> >
> > Make sure the numbers on both sheets are the same type of data.
> > Those leading zeroes could mean your numbers might be text in one of the
> > sheets and not in the other.
> > Also, your dates look questionable.
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> >
> > "RFreeman12" <RFreeman12@discussions.microsoft.com> wrote in message
> > news:FEB32457-17BD-4A31-BE3B-F86C2A03E72F@microsoft.com...
> > > I have a spreadsheet that has two worksheet. Worksheet "start" has
two
> > > columns - Column A is a date Column B is a number - rows contain
dates
> > and
> > > continuous numbers EX Row 1 = 0000417 Row 2 = 0000418 Row 3 =
> > 000419 -
> > > each with a date of 060105.
> > >
> > > Worksheet "clear" has two columns - basically the same as Worksheet
> > "start"
> > > - difference is they are not in sequence and not always the same
numbers
> > and
> > > they are scrambled. I don't want to sort this data.
> > > I would like to use Worksheet "Clear" - Take the first Row and
determine
> > > if the first Number is on Worksheet "start" - if it is then tell me
what
> > the
> > > date is from Worksheet "start" - if not then leave it blank and
repeat
> > this
> > > for all given numbers.
> > >
> > > I am unable to get the RANGE to work for each individual cell on
Worksheet
> > > "clear". It works cell by cell but not by range.
> > >
> > > EX of formula tested
> > > IF((CLEAR!A1=START!A1:A25),START!B1,"NONE")
> > >
> > > The results show {TRUE;FALSE;FALSE....
> > > So that the last value is the only thing that appears in this cell -
> > > therefore getting a NONE result because the last result is FALSE...
once
> > the
> > > value is met - I'd like to search the rest of the cells to pull each
date
> > for
> > > each item...
> > >
> >
> >