Prev: Pulling data from other spreadsheets
Next: math
From: Mike H Mike on 12 Apr 2010 17:41
I need an Excel 2003 formula: If the first cell contains the letter Y then a
second cell will display a name contained in a third cell - The second cell
needs the formula.
From: Joe User on 12 Apr 2010 18:17
"Mike H" wrote:
> I need an Excel 2003 formula: If the first cell contains
> the letter Y then a second cell will display a name
> contained in a third cell - The second cell needs the
> formula.

If "Y" will be the entire cell value, then in the second cell (A1 and A3
represent the first and third cells):

=if(A1="Y", C1, "")

Note that this returns the null string ("") if A1 does not contain "Y", a
condition you neglected to cover.

If "Y" will always be the first character of the cell, then:

=if(left(A1,1)="Y", C1, "")

If "Y" might be anywhere within the cell, and you want to recognize "y"
(lowercase) as well, then:

=if(isnumber(search("y",A1)), C1, "")

If you want to recognize only "Y" (uppercase), change SEARCH to FIND, and
change "y" to "Y". But note that the other two formulas recognize match "y"
and "Y". See the EXACT function if you need to distinguish between them.
From: Dave Peterson on 12 Apr 2010 18:22
=if(isnumber(search("y",A1)),A3,"No Y")
=search() is not case sensitive (Y and y will both be ok).

If you need to match case, use =find() instead of =search().

Another non-case sensitive way:
=if(countif(a1,"*y*")>0,A3,"No Y")

I'm checking A1 and using the name in A3. My formula was in A2.

Mike H wrote:
>
> I need an Excel 2003 formula: If the first cell contains the letter Y then a
> second cell will display a name contained in a third cell - The second cell
> needs the formula.

--

Dave Peterson
 | 
Pages: 1
Prev: Pulling data from other spreadsheets
Next: math