Thread: compare column1 with column2

Hi,

How do i compare column1 with column2 and output in column3.

ex.

column1 column2 output

test1 test4 test1
test2 test6 test2
test4 test1 test4
test6 test2 test6

Thanks for you help.

You have not told use what criterion to use in the output column.
Do you want the largest of the two values?
either =MAX(A1:B1) or IF(A1>B1,A1,B1)
copy down the column
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"myguess21" <myguess21.1zxvin_1134411004.1056@excelforum-nospam.com> wrote
in message news:myguess21.1zxvin_1134411004.1056@excelforum-nospam.com...
>
> Hi,
>
> How do i compare column1 with column2 and output in column3.
>
> ex.
>
> column1 column2 output
>
> test1 test4 test1
> test2 test6 test2
> test4 test1 test4
> test6 test2 test6
>
> Thanks for you help.
>
>
> --
> myguess21
> ------------------------------------------------------------------------
> myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> View this thread: http://www.excelforum.com/showthread...hreadid=492781
>

Thanks for quick response.

No this is string. Just want to search the value from column 2 in column1 if found out put in column 3.

Some formulas to try:
=IF(A1=B1,A1,"")
=IF(FIND(B1,A1),A1,"")
=IF(SEARCH(B1,A1),A1,"")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
>
> Thanks for quick response.
>
> No this is string. Just want to search the value from column 2 in
> column1 if found out put in column 3.
>
>
> --
> myguess21
> ------------------------------------------------------------------------
> myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> View this thread: http://www.excelforum.com/showthread...hreadid=492781
>

in C1
=if(match(B1,A:A,0),A1,"")

then drag fill down the column.

--
Regards,
Tom Ogilvy

"myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
>
> Thanks for quick response.
>
> No this is string. Just want to search the value from column 2 in
> column1 if found out put in column 3.
>
>
> --
> myguess21
> ------------------------------------------------------------------------
> myguess21's Profile:
http://www.excelforum.com/member.php...o&userid=29578
> View this thread: http://www.excelforum.com/showthread...hreadid=492781
>

Oh dear I mean to use MATCH not FIND!
Time to retire!!!

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:ejO2B70$FHA.1032@TK2MSFTNGP11.phx.gbl...
> Some formulas to try:
> =IF(A1=B1,A1,"")
> =IF(FIND(B1,A1),A1,"")
> =IF(SEARCH(B1,A1),A1,"")
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote
> in message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
>>
>> Thanks for quick response.
>>
>> No this is string. Just want to search the value from column 2 in
>> column1 if found out put in column 3.
>>
>>
>> --
>> myguess21
>> ------------------------------------------------------------------------
>> myguess21's Profile:
>> http://www.excelforum.com/member.php...o&userid=29578
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=492781
>>
>
>

Is this is what you meant? (Search col A for each element in col B?)

=IF(MATCH(B1,A:A,0),B1,"")

red blue blue
green white white
blue yellow #N/A
black black black
white blue blue

but in both cases how do you get rid of "#N/A" when there is no match?

"Tom Ogilvy" wrote:

> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread: http://www.excelforum.com/showthread...hreadid=492781
> >
>
>
>

Should actually be:

=if(isnumber(match(B1,A:A,0)),A1,"")

--
Regards,
Tom Ogilvy



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23anPy60$FHA.2996@TK2MSFTNGP09.phx.gbl...
> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote
in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread:
http://www.excelforum.com/showthread...hreadid=492781
> >
>
>

Not if you look at the OP's example. It is as I wrote it and I posted a
correction to account for the N/A.

--
Regards,
Tom Ogilvy



"Charlie" <Charlie@discussions.microsoft.com> wrote in message
news:AEDA1FFF-7EB0-4BEF-B73A-610F60AB61DA@microsoft.com...
> Is this is what you meant? (Search col A for each element in col B?)
>
> =
> IF(MATCH(B1,A:A,0),B1,"")
> red blue blue
> green white white
> blue yellow #N/A
> black black black
> white blue blue
>
> but in both cases how do you get rid of "#N/A" when there is no match?
>
> "Tom Ogilvy" wrote:
>
> > in C1
> > =if(match(B1,A:A,0),A1,"")
> >
> > then drag fill down the column.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com>
wrote in
> > message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> > >
> > > Thanks for quick response.
> > >
> > > No this is string. Just want to search the value from column 2 in
> > > column1 if found out put in column 3.
> > >
> > >
> > > --
> > > myguess21
> >
> ------------------------------------------------------------------------
> > > myguess21's Profile:
> > http://www.excelforum.com/member.php...o&userid=29578
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=492781
> > >
> >
> >
> >

The original example seems a little unusual. Maybe it's a VLookup problem:

=VLOOKUP(B1,A:B,2,FALSE)

test1 test4 test3
test2 test6 test5
test4 test3 test1
test6 test5 #N/A
test3 test1 test4


"Tom Ogilvy" wrote:

> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread: http://www.excelforum.com/showthread...hreadid=492781
> >
>
>
>

Yes. this is what i need to do.

Thanks a lot.

Originally Posted by Charlie

Is this is what you meant? (Search col A for each element in col B?)

=IF(MATCH(B1,A:A,0),B1,"")

red blue blue
green white white
blue yellow #N/A
black black black
white blue blue

but in both cases how do you get rid of "#N/A" when there is no match?

"Tom Ogilvy" wrote:

> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread: http://www.excelforum.com/showthread...hreadid=492781
> >
>
>
>

P.S. thanks for the #N/A correction

"Tom Ogilvy" wrote:

> Not if you look at the OP's example. It is as I wrote it and I posted a
> correction to account for the N/A.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Charlie" <Charlie@discussions.microsoft.com> wrote in message
> news:AEDA1FFF-7EB0-4BEF-B73A-610F60AB61DA@microsoft.com...
> > Is this is what you meant? (Search col A for each element in col B?)
> >
> > =
> > IF(MATCH(B1,A:A,0),B1,"")
> > red blue blue
> > green white white
> > blue yellow #N/A
> > black black black
> > white blue blue
> >
> > but in both cases how do you get rid of "#N/A" when there is no match?
> >
> > "Tom Ogilvy" wrote:
> >
> > > in C1
> > > =if(match(B1,A:A,0),A1,"")
> > >
> > > then drag fill down the column.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com>
> wrote in
> > > message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> > > >
> > > > Thanks for quick response.
> > > >
> > > > No this is string. Just want to search the value from column 2 in
> > > > column1 if found out put in column 3.
> > > >
> > > >
> > > > --
> > > > myguess21
> > >
> > ------------------------------------------------------------------------
> > > > myguess21's Profile:
> > > http://www.excelforum.com/member.php...o&userid=29578
> > > > View this thread:
> http://www.excelforum.com/showthread...hreadid=492781
> > > >
> > >
> > >
> > >
>
>
>