I'm Trying to Construct a Smart Worksheet

[Santi]

Hi,

I’m trying to construct a smart worksheet so when I enter a number or any
value it will automatically give me a specific result if it meets the
requirements from a first worksheet. This is an example of what I’m trying to
accomplish, please if anyone can assist I will be very grateful.

Sheet 1 Sheet 2
A A A

1 20 1 704080000112 1
2 30 2 2
3 7 3 3 IF(LEFT(A1,2)=Sheet1!
A1:A5,"CHARD"," ")


Basically I want to use a formula that when the first to digits of a number
is equal to any of the numbers in sheet 1 the result would be any word I want
(exampleâ€Chardâ€) if not then I want the cell to be blank.

 

[Pete_UK]

Try something like this:

=IF(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A$5,0)),"","your_word")

Copy this down to suit. Assumes your have proper numbers in Sheet1,
rather than text values.

Hope this helps.

Pete

 

[Santi]

Pete,

That worked perfectly! But do you know if I can use your formula for
multiple answers where lets say the numbers in range A$1:A$5 in sheet 1 does
not contain any of the numbers specified in Left(A1,2)? But maybe the number
is located in a second range which is A$7:A$12.

 

[Ken Johnson]

Pete,

That worked perfectly! But do you know if I can use your formula for
multiple answers where lets say the numbers in range A$1:A$5 in sheet 1 does
not contain any of the numbers specified in Left(A1,2)? But maybe the number
is located in a second range which is A$7:A$12.

You could nest an AND function inside Pete's formula to test each
separate area in Sheet1! column A...

=IF(AND(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A
$5,0)),ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$7:A$12,0))),"","your_word")

inside the AND function there will need to be a
ISNA(MATCH(LEFT(A1,2)*1,Range_Address,0)) for each separate range
being tested for the presence of LEFT(A1,2)*1.

I have assumed that the values in Sheet1! column A separating the
areas to be searched (A6 in the example) contain values that would
interfere with the search carried out by the MATCH function. If this
is not the case then all you would have to do is extend the
lookup_array's address to include as much of Sheet1! column A as
required, eg...

=IF(ISNA(MATCH(LEFT(A1,2)*1,Sheet1!A$1:A$12,0)),"","your_word")

Ken Johnson