Match text in one column to another, return number for each match

[Longshot9]

Hi,

I have 2 columns of text, one is C5:C18. The other is J27:J40. C12 and J34 will always be blank. I would like to compare the 2 columns and return 3 for each match in the same slot. I have a function doing it now but it's extremely long and hard to manage when I need to change the J column to L or AA or whatever. The C column numbers will never change.

Example:

C5 Reds
C6 Orioles
C7 Padres
C8 Giants
C9 Cubs
C10 White Sox
C11 Mets

C13 Dodgers
C14 Athletics
C15 Astros
C16 Rangers
C17 Cardinals
C18 Marlins

Then the J column

J27 Indians
J28 Padres
J29 Rays
J30 Giants
J31 Tigers
J32 Yankees
J33 Diamondbacks

J35 Dodgers
J36 Mariners
J37 Brewers
J38 Braves
J39 Cardinals
J40 Athletics

They need to match in the exact same spot. So for example, even though the Padres are in both the C and J columns, they don't match up exactly (C7 to J29). God I hope that makes sense.

The formula/result for this one would go in H3, and should return 9 because the Giants, Dodgers, and Cardinals were the only ones that matched in the same spots.

Here's the long formula I currently have...

=IF($C$5=J$27,3,0)+IF($C$6=J$28,3,0)+IF($C$7=J$29,3,0)+IF($C$8=J$30,3,0)+IF($C$9=J$31,3,0)+IF($C$10=J$32,3,0)+IF($C$11=J$33,3,0)+IF($C$13=J$35,3,0)+IF($C$14=J$36,3,0)+IF($C$15=J$37,3,0)+IF($C$16=J$38,3,0)+IF($C$17=J$39,3,0)+IF($C$18=J$40,3,0)

Just looking for a way to shrink this down and make it easier to manage.

Thanks!

 

[ukmikeb]

Hi

Try -

=(SUM(--($C$5:$C$18=$J$27:$J$40))-1)*3

entered with Control-Shift-Enter

hth

 

[Longshot9]

Hi

Try -

=(SUM(--($C$5:$C$18=$J$27:$J$40))-1)*3

entered with Control-Shift-Enter

hth

Awesome, thanks so much Mike

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(1-(C5:C18=""),(C5:C18=J27:J40)*3)

Hi,

I have 2 columns of text, one is C5:C18. The other is J27:J40. C12 and J34 will always be blank. I would like to compare the 2 columns and return 3 for each match in the same slot. I have a function doing it now but it's extremely long and hard to manage when I need to change the J column to L or AA or whatever. The C column numbers will never change.

Example:

C5 Reds
C6 Orioles
C7 Padres
C8 Giants
C9 Cubs
C10 White Sox
C11 Mets

C13 Dodgers
C14 Athletics
C15 Astros
C16 Rangers
C17 Cardinals
C18 Marlins

Then the J column

J27 Indians
J28 Padres
J29 Rays
J30 Giants
J31 Tigers
J32 Yankees
J33 Diamondbacks

J35 Dodgers
J36 Mariners
J37 Brewers
J38 Braves
J39 Cardinals
J40 Athletics

They need to match in the exact same spot. So for example, even though the Padres are in both the C and J columns, they don't match up exactly (C7 to J29). God I hope that makes sense.

The formula/result for this one would go in H3, and should return 9 because the Giants, Dodgers, and Cardinals were the only ones that matched in the same spots.

Here's the long formula I currently have...

=IF($C$5=J$27,3,0)+IF($C$6=J$28,3,0)+IF($C$7=J$29,3,0)+IF($C$8=J$30,3,0)+IF($C$9=J$31,3,0)+IF($C$10=J$32,3,0)+IF($C$11=J$33,3,0)+IF($C$13=J$35,3,0)+IF($C$14=J$36,3,0)+IF($C$15=J$37,3,0)+IF($C$16=J$38,3,0)+IF($C$17=J$39,3,0)+IF($C$18=J$40,3,0)

Just looking for a way to shrink this down and make it easier to manage.

Thanks!

 

[T. Valko]

Hi,

I have 2 columns of text, one is C5:C18. The other is J27:J40. C12 and J34 will always be blank. I would like to compare the 2 columns and return 3 for each match in the same slot. I have a function doing it now but it's extremely long and hard to manage when I need to change the J column to L or AA or whatever. The C column numbers will never change.

Example:

C5 Reds
C6 Orioles
C7 Padres
C8 Giants
C9 Cubs
C10 White Sox
C11 Mets

C13 Dodgers
C14 Athletics
C15 Astros
C16 Rangers
C17 Cardinals
C18 Marlins

Then the J column

J27 Indians
J28 Padres
J29 Rays
J30 Giants
J31 Tigers
J32 Yankees
J33 Diamondbacks

J35 Dodgers
J36 Mariners
J37 Brewers
J38 Braves
J39 Cardinals
J40 Athletics

They need to match in the exact same spot. So for example, even though the Padres are in both the C and J columns, they don't match up exactly (C7 to J29). God I hope that makes sense.

The formula/result for this one would go in H3, and should return 9 because the Giants, Dodgers, and Cardinals were the only ones that matched in the same spots.

Here's the long formula I currently have...

=IF($C$5=J$27,3,0)+IF($C$6=J$28,3,0)+IF($C$7=J$29,3,0)+IF($C$8=J$30,3,0)+IF($C$9=J$31,3,0)+IF($C$10=J$32,3,0)+IF($C$11=J$33,3,0)+IF($C$13=J$35,3,0)+IF($C$14=J$36,3,0)+IF($C$15=J$37,3,0)+IF($C$16=J$38,3,0)+IF($C$17=J$39,3,0)+IF($C$18=J$40,3,0)

Just looking for a way to shrink this down and make it easier to manage.

Thanks!

Here's another one...

=SUMPRODUCT(--(C5:C18<>""),--(C5:C18=J27:J40))*3