matching entries, a tricky one

drag-driver · Mar 26th, 2002, 04:52 AM

My problem is such i have an entry in a colum "2690 - L 6MT1 REG" i want to be able to compare the first 4 digits with a number in another colum and return the value true or false depending on wither they are matched.

Aladin Akyurek · Mar 26th, 2002, 04:56 AM

Quote:

On 2002-03-26 03:52, drag-driver wrote:
My problem is such i have an entry in a colum "2690 - L 6MT1 REG" i want to be able to compare the first 4 digits with a number in another colum and return the value true or false depending on wither they are matched.

=ISNUMBER(MATCH(LEFT(A1,4)+0,B:B,0))

Substitute the true range in B for B:B.

Aladin

AJ · Mar 26th, 2002, 04:56 AM

This should do the trick

=IF(LEFT(A1,4)+0=C1,TRUE)

The LEFT(A1,4) extracts the first four characters.
The +0, converts it to number format (LEFT produces text by default)
C1 is where I put the four digit value by itself
Then there's just a bog standard IF around it.

Rgds
AJ

Dave Hawley · Mar 26th, 2002, 04:57 AM

Hi

How about

=IF(COUNTIF($C$1:$C$600,LEFT(A1,4)),TRUE)

Aladin Akyurek · Mar 26th, 2002, 05:04 AM

Or, indeed, with COUNTIF:

=COUNTIF(B:B,LEFT(A1,4)+0)>0

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-26 04:18 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 04:52 AM	#1
drag-driver Board Regular Join Date: Feb 2002 Posts: 91	My problem is such i have an entry in a colum "2690 - L 6MT1 REG" i want to be able to compare the first 4 digits with a number in another colum and return the value true or false depending on wither they are matched.

Mar 26th, 2002, 04:56 AM	#3
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	This should do the trick =IF(LEFT(A1,4)+0=C1,TRUE) The LEFT(A1,4) extracts the first four characters. The +0, converts it to number format (LEFT produces text by default) C1 is where I put the four digit value by itself Then there's just a bog standard IF around it. Rgds AJ

Mar 26th, 2002, 04:57 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi How about =IF(COUNTIF($C$1:$C$600,LEFT(A1,4)),TRUE)

Mar 26th, 2002, 05:04 AM	#5
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Or, indeed, with COUNTIF: =COUNTIF(B:B,LEFT(A1,4)+0)>0 Aladin [ This Message was edited by: Aladin Akyurek on 2002-03-26 04:18 ]