Help Please

I am looking for a formula that will search 2 columns for identical matches
and when found return a value from a third colum.

Thank you.

DD

=IF(A1=B1,C1,"")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"D. D. Scopes" <[email protected]> wrote in message
news:[email protected]...
>I am looking for a formula that will search 2 columns for identical matches
> and when found return a value from a third colum.
>
> Thank you.
>
> DD
>
>

Try

=INDEX(C1:C100,MATCH(1,(A1:A100="Lookup1")*(B1:B100="Lookup2"),0))

where C1:C100 contains the values (3rd column)

You must enter this with CTRL SHIFT ENTER.

It works by finding a 1 in the array of numbers returned by the
A1:A100="Lookup1"*B1:B100="Lookup2". When there is not lookup1 and lookup2
in the same row, a 0 will be returned. When they are in the same row, a 1
will be returned, which will be matched by the 1 you're looking for, and the
position will be used by the INDEX part to give you the number.



"D. D. Scopes" <[email protected]> wrote in message
news:[email protected]...
> I am looking for a formula that will search 2 columns for identical
matches
> and when found return a value from a third colum.
>
> Thank you.
>
> DD
>
>

Dave tried it and i gives an #N/A error.

Say the formula is in column D.

I am looking for it to return the value in C3 if there is a match for B3
anywhere in column A.

Thanks.

DD


"Dave R." <[email protected]> wrote in message
news:%[email protected]...
> Try
>
> =INDEX(C1:C100,MATCH(1,(A1:A100="Lookup1")*(B1:B100="Lookup2"),0))
>
> where C1:C100 contains the values (3rd column)
>
> You must enter this with CTRL SHIFT ENTER.
>
> It works by finding a 1 in the array of numbers returned by the
> A1:A100="Lookup1"*B1:B100="Lookup2". When there is not lookup1 and lookup2
> in the same row, a 0 will be returned. When they are in the same row, a 1
> will be returned, which will be matched by the 1 you're looking for, and
the
> position will be used by the INDEX part to give you the number.
>
>
>
> "D. D. Scopes" <[email protected]> wrote in message
> news:[email protected]...
> > I am looking for a formula that will search 2 columns for identical
> matches
> > and when found return a value from a third colum.
> >
> > Thank you.
> >
> > DD
> >
> >
>
>

Try this and copy down

=IF(COUNTIF(A:A,B1)>0,C1,"")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"D. D. Scopes" <[email protected]> wrote in message
news:[email protected]...
> Dave tried it and i gives an #N/A error.
>
> Say the formula is in column D.
>
> I am looking for it to return the value in C3 if there is a match for B3
> anywhere in column A.
>
> Thanks.
>
> DD
>
>
> "Dave R." <[email protected]> wrote in message
> news:%[email protected]...
>> Try
>>
>> =INDEX(C1:C100,MATCH(1,(A1:A100="Lookup1")*(B1:B100="Lookup2"),0))
>>
>> where C1:C100 contains the values (3rd column)
>>
>> You must enter this with CTRL SHIFT ENTER.
>>
>> It works by finding a 1 in the array of numbers returned by the
>> A1:A100="Lookup1"*B1:B100="Lookup2". When there is not lookup1 and
>> lookup2
>> in the same row, a 0 will be returned. When they are in the same row, a 1
>> will be returned, which will be matched by the 1 you're looking for, and
> the
>> position will be used by the INDEX part to give you the number.
>>
>>
>>
>> "D. D. Scopes" <[email protected]> wrote in message
>> news:[email protected]...
>> > I am looking for a formula that will search 2 columns for identical
>> matches
>> > and when found return a value from a third colum.
>> >
>> > Thank you.
>> >
>> > DD
>> >
>> >
>>
>>
>
>

Thanks Peo:

That did not quite do it. Let me explain again.

Cell A1 has the name Joe Blow.

Cell B1 has has Ford.

Cell C250 has the name Joe Bow.

I want the formula in D250 to search all of column A for the match to C250
(Joe Blow) and if there is a match in cloumn A (there is in cell A1) return
the value Ford from B1 to cell D250.

Thanks again.



"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> Try this and copy down
>
> =IF(COUNTIF(A:A,B1)>0,C1,"")
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please, for everyone's
> benefit keep the discussion in the newsgroup/forum)
>
>
>
> "D. D. Scopes" <[email protected]> wrote in message
> news:[email protected]...
> > Dave tried it and i gives an #N/A error.
> >
> > Say the formula is in column D.
> >
> > I am looking for it to return the value in C3 if there is a match for B3
> > anywhere in column A.
> >
> > Thanks.
> >
> > DD
> >
> >
> > "Dave R." <[email protected]> wrote in message
> > news:%[email protected]...
> >> Try
> >>
> >> =INDEX(C1:C100,MATCH(1,(A1:A100="Lookup1")*(B1:B100="Lookup2"),0))
> >>
> >> where C1:C100 contains the values (3rd column)
> >>
> >> You must enter this with CTRL SHIFT ENTER.
> >>
> >> It works by finding a 1 in the array of numbers returned by the
> >> A1:A100="Lookup1"*B1:B100="Lookup2". When there is not lookup1 and
> >> lookup2
> >> in the same row, a 0 will be returned. When they are in the same row, a
1
> >> will be returned, which will be matched by the 1 you're looking for,
and
> > the
> >> position will be used by the INDEX part to give you the number.
> >>
> >>
> >>
> >> "D. D. Scopes" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I am looking for a formula that will search 2 columns for identical
> >> matches
> >> > and when found return a value from a third colum.
> >> >
> >> > Thank you.
> >> >
> >> > DD
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Got you! In D1 put this formula and copy down

=IF(ISNUMBER(MATCH(C1,A:A,0)),INDEX(B:B,MATCH(C1,A:A,0)),"No Match")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"D. D. Scopes" <[email protected]> wrote in message
news:[email protected]...
> Thanks Peo:
>
> That did not quite do it. Let me explain again.
>
> Cell A1 has the name Joe Blow.
>
> Cell B1 has has Ford.
>
> Cell C250 has the name Joe Bow.
>
> I want the formula in D250 to search all of column A for the match to C250
> (Joe Blow) and if there is a match in cloumn A (there is in cell A1)
> return
> the value Ford from B1 to cell D250.
>
> Thanks again.
>
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
>> Try this and copy down
>>
>> =IF(COUNTIF(A:A,B1)>0,C1,"")
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> (No private emails please, for everyone's
>> benefit keep the discussion in the newsgroup/forum)
>>
>>
>>
>> "D. D. Scopes" <[email protected]> wrote in message
>> news:[email protected]...
>> > Dave tried it and i gives an #N/A error.
>> >
>> > Say the formula is in column D.
>> >
>> > I am looking for it to return the value in C3 if there is a match for
>> > B3
>> > anywhere in column A.
>> >
>> > Thanks.
>> >
>> > DD
>> >
>> >
>> > "Dave R." <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> Try
>> >>
>> >> =INDEX(C1:C100,MATCH(1,(A1:A100="Lookup1")*(B1:B100="Lookup2"),0))
>> >>
>> >> where C1:C100 contains the values (3rd column)
>> >>
>> >> You must enter this with CTRL SHIFT ENTER.
>> >>
>> >> It works by finding a 1 in the array of numbers returned by the
>> >> A1:A100="Lookup1"*B1:B100="Lookup2". When there is not lookup1 and
>> >> lookup2
>> >> in the same row, a 0 will be returned. When they are in the same row,
>> >> a
> 1
>> >> will be returned, which will be matched by the 1 you're looking for,
> and
>> > the
>> >> position will be used by the INDEX part to give you the number.
>> >>
>> >>
>> >>
>> >> "D. D. Scopes" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > I am looking for a formula that will search 2 columns for identical
>> >> matches
>> >> > and when found return a value from a third colum.
>> >> >
>> >> > Thank you.
>> >> >
>> >> > DD
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>