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)

 

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

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

 

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

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]

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]

Peo:

You are the man.

Thank you much.

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)

 

[Peo Sjoblom]

My Pleasure

--
Regards,

Peo Sjoblom

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

 

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

Yes it would

--
Regards,

Peo Sjoblom

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