Formula to match the first letter of one column with the first letter in another column. (If/And statement)

[TrudyP]

I'm working off two sheets. Sheet 1 has two columns like this:
[TABLE="width: 203"]
<tbody>[TR]
[TD]Adjective[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Adventurous[/TD]
[TD]Guppy[/TD]
[/TR]
[TR]
[TD]Aged[/TD]
[TD]Lamprey[/TD]
[/TR]
[TR]
[TD]Boring[/TD]
[TD]Bluegill[/TD]
[/TR]
[TR]
[TD]Brass[/TD]
[TD]Spot[/TD]
[/TR]
[TR]
[TD]Heated[/TD]
[TD]Cod[/TD]
[/TR]
[TR]
[TD]Helpful[/TD]
[TD]Shiner[/TD]
[/TR]
[TR]
[TD]High[/TD]
[TD]Spot[/TD]
[/TR]
[TR]
[TD]Long[/TD]
[TD]Ling[/TD]
[/TR]
[TR]
[TD]Simple[/TD]
[TD]Shad[/TD]
[/TR]
</tbody>[/TABLE]

On sheet two, I'm trying to write a formula to determine if the first letter in column 1 (sheet 1) equals the first letter in column 2 (sheet 1). Here's what I've written, but it just keeps returning "No Match", even though I know (for example) that Boring Bluegill and Long Ling meet my criteria.

=IF(AND(LEFT(Ships!D:D,1)=A1,LEFT(Ships!E:E,1)=A1),"Match","No Match")

Sheet 2 looks like this. I want "B", "L" and "S" (on the list below) to display the message "Match".
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"]No Match[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]V[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]W[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]No Match[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your help!

 

[T. Valko]

I'm working off two sheets. Sheet 1 has two columns like this:
[TABLE="width: 203"]
<TBODY>[TR]
[TD]Adjective
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]Adventurous
[/TD]
[TD]Guppy
[/TD]
[/TR]
[TR]
[TD]Aged
[/TD]
[TD]Lamprey
[/TD]
[/TR]
[TR]
[TD]Boring
[/TD]
[TD]Bluegill
[/TD]
[/TR]
[TR]
[TD]Brass
[/TD]
[TD]Spot
[/TD]
[/TR]
[TR]
[TD]Heated
[/TD]
[TD]Cod
[/TD]
[/TR]
[TR]
[TD]Helpful
[/TD]
[TD]Shiner
[/TD]
[/TR]
[TR]
[TD]High
[/TD]
[TD]Spot
[/TD]
[/TR]
[TR]
[TD]Long
[/TD]
[TD]Ling
[/TD]
[/TR]
[TR]
[TD]Simple
[/TD]
[TD]Shad
[/TD]
[/TR]
</TBODY>[/TABLE]

On sheet two, I'm trying to write a formula to determine if the first letter in column 1 (sheet 1) equals the first letter in column 2 (sheet 1). Here's what I've written, but it just keeps returning "No Match", even though I know (for example) that Boring Bluegill and Long Ling meet my criteria.

=IF(AND(LEFT(Ships!D:D,1)=A1,LEFT(Ships!E:E,1)=A1),"Match","No Match")

Sheet 2 looks like this. I want "B", "L" and "S" (on the list below) to display the message "Match".
[TABLE="width: 128"]
<TBODY>[TR]
[TD="width: 64"]A
[/TD]
[TD="width: 64"]No Match
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]G
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]I
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]J
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]K
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]L
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]M
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]O
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]P
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]Q
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]S
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]T
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]U
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]V
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]W
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]No Match
[/TD]
[/TR]
[TR]
[TD]Z
[/TD]
[TD]No Match
[/TD]
[/TR]
</TBODY>[/TABLE]

Thanks in advance for your help!

What version of Excel are you using?

 

[Special-K99]

on Sheet2
=IF(SUMPRODUCT(--(LEFT('Ships'!A$1:A$10,1)=LEFT('Ships'!B$1:B$10,1))*(LEFT('Ships'!A$1:A$10,1)=A1))=0,"No ","")&"Match"
and copy down

where
Sheet 2 column A are the letters A-Z

 

[Aladin Akyurek]

Sheet1 is I assume Ships in your wb...

B1 on Sheet2, control+shift+enter, not just enter, and copy down:

=IFERROR(IF(LEFT(INDEX(Ships!$E$2:$E$10,MATCH(A1,LEFT(Ships!$D$2:$D$10,1),0)),1)=A1,"match","no match"),"")

Note. IFERROR is available on 2007 systems and later. It can be omitted or replaced if not available.

 

[T. Valko]

What version of Excel are you using?

If you're using Excel 2007 or later...

Sheet2 A2:A27 = letters

Enter this formula on Sheet2 in cell B2 and copy down:

=IF(COUNTIFS(Ships!D:D,A2&"*",Ships!E:E,A2&"*"),"Match","No Match")

 

[TrudyP]

If you're using Excel 2007 or later...

Sheet2 A2:A27 = letters

Enter this formula on Sheet2 in cell B2 and copy down:

=IF(COUNTIFS(Ships!D:D,A2&"*",Ships!E:E,A2&"*"),"Match","No Match")

Thanks, this one worked for me. I learned something new!

 

[Aladin Akyurek]

Thanks, this one worked for me. I learned something new!

Just for the record: The one with index/Match will work too. Modified a bit, it might even be faster...

=IFERROR(IF(LEFT(INDEX(Ships!$E$2:$E$10,MATCH(A1&"*",Ships!$D$2:$D$10,0)),1)=A1,"match","no match"),"")

which needs now just enter.

 

[T. Valko]

Thanks, this one worked for me. I learned something new!

You're welcome. Thanks for the feedback!