Need help with a lookup or if statement
 

A B C
Samson 0.5 0.5
Samson Co. 0.3 0.3
Ace 0.7 0.7
Ace Co. 0.2 0.2
Peterson 0.8 0
Derby 0.6 0
Samson 0.9 0.9
Samson Co. 0.4 0.4
Ace 0.1 0.1
Ace Co. 0.3 0.3
Peterson 0.2 0
Derby 0.5 0

Entities:
Samson
Ace

If Col. A is equal to or starts with one of the names in the range
'Entities', I want Col. C to return the contents of Col. B, and if hot, to
return '0'. Any suggestions? Thanks!

Need help with a lookup or if statement
 

TRY THIS for c1:

=if(or(left(a1,3)="Sam",left(a1,3)="Ace")=TRUE,B1, 0)
--
hope to help,
cm


"Franci" wrote:

A B C
Samson 0.5 0.5
Samson Co. 0.3 0.3
Ace 0.7 0.7
Ace Co. 0.2 0.2
Peterson 0.8 0
Derby 0.6 0
Samson 0.9 0.9
Samson Co. 0.4 0.4
Ace 0.1 0.1
Ace Co. 0.3 0.3
Peterson 0.2 0
Derby 0.5 0

Entities:
Samson
Ace

If Col. A is equal to or starts with one of the names in the range
'Entities', I want Col. C to return the contents of Col. B, and if hot, to
return '0'. Any suggestions? Thanks!

Need help with a lookup or if statement
 

One way...

Assume Entities is in the range J2:J3

Enter this array formula** in B2 and copy down as needed:

=IF(COUNT(SEARCH(J$2:J$3,A2)),B2,0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Franci" wrote in message
...
A B C
Samson 0.5 0.5
Samson Co. 0.3 0.3
Ace 0.7 0.7
Ace Co. 0.2 0.2
Peterson 0.8 0
Derby 0.6 0
Samson 0.9 0.9
Samson Co. 0.4 0.4
Ace 0.1 0.1
Ace Co. 0.3 0.3
Peterson 0.2 0
Derby 0.5 0

Entities:
Samson
Ace

If Col. A is equal to or starts with one of the names in the range
'Entities', I want Col. C to return the contents of Col. B, and if hot, to
return '0'. Any suggestions? Thanks!

Need help with a lookup or if statement
 

Ooops!

Enter this array formula** in B2 and copy down as needed:

Should be:

Enter this array formula** in C2 and copy down as needed:

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

Assume Entities is in the range J2:J3

Enter this array formula** in B2 and copy down as needed:

=IF(COUNT(SEARCH(J$2:J$3,A2)),B2,0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Franci" wrote in message
...
A B C
Samson 0.5 0.5
Samson Co. 0.3 0.3
Ace 0.7 0.7
Ace Co. 0.2 0.2
Peterson 0.8 0
Derby 0.6 0
Samson 0.9 0.9
Samson Co. 0.4 0.4
Ace 0.1 0.1
Ace Co. 0.3 0.3
Peterson 0.2 0
Derby 0.5 0

Entities:
Samson
Ace

If Col. A is equal to or starts with one of the names in the range
'Entities', I want Col. C to return the contents of Col. B, and if hot,
to
return '0'. Any suggestions? Thanks!

Need help with a lookup or if statement
 

You don't need =TRUE in your IF statement.

You could simplify
=if(or(left(a1,3)="Sam",left(a1,3)="Ace")=TRUE,B1, 0)
to
=if(or(left(a1,3)="Sam",left(a1,3)="Ace"),B1,0)
or to
=if(or(left(A1,3)={"Sam","Ace"}),B1,0)
--
David Biddulph


cm wrote:
TRY THIS for c1:

=if(or(left(a1,3)="Sam",left(a1,3)="Ace")=TRUE,B1, 0)

A B C
Samson 0.5 0.5
Samson Co. 0.3 0.3
Ace 0.7 0.7
Ace Co. 0.2 0.2
Peterson 0.8 0
Derby 0.6 0
Samson 0.9 0.9
Samson Co. 0.4 0.4
Ace 0.1 0.1
Ace Co. 0.3 0.3
Peterson 0.2 0
Derby 0.5 0

Entities:
Samson
Ace

If Col. A is equal to or starts with one of the names in the range
'Entities', I want Col. C to return the contents of Col. B, and if
hot, to return '0'. Any suggestions? Thanks!