Help Please

[D. D. Scopes]

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

[Peo Sjoblom]

=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
>
>

[Dave R.]

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
>
>

[D. D. Scopes]

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
> >
> >
>
>

[Peo Sjoblom]

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
>> >
>> >
>>
>>
>
>

[D. D. Scopes]

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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Peo Sjoblom]

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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[D. D. Scopes]

Peo:

You are the man.

Thank you much.

DD


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Peo Sjoblom]

My Pleasure

--
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]...
> Peo:
>
> You are the man.
>
> Thank you much.
>
> DD
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[JulieD]

Hi Peo

might be missing something here, but wouldn't VLOOKUP do the same?
=VLOOKUP(C250,A1:B200,2,0)
and with a bit of error handling
=IF(ISNA(VLOOKUP(C250,A1:B200,2,0)),"no match",VLOOKUP(C250,A1:B200,2,0))

Cheers
JulieD


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> 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
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

[Peo Sjoblom]

Yes it would

--
Regards,

Peo Sjoblom

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



"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Peo
>
> might be missing something here, but wouldn't VLOOKUP do the same?
> =VLOOKUP(C250,A1:B200,2,0)
> and with a bit of error handling
> =IF(ISNA(VLOOKUP(C250,A1:B200,2,0)),"no match",VLOOKUP(C250,A1:B200,2,0))
>
> Cheers
> JulieD
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>>> >> >
>>>> >> >
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
>