IF function with multiple criteria including a LEFT and INDEX/MATCH

[sjames1066]

Hi all,

Help would be gratefully received in my current excel dilemma.

In my sheet I'm looking for a formula which gives me either 1 or 2 initials of a first name depending on whether the surname appears more than once and if the first name has the same first letter i.e.

B Surname	C Firstname	D Initial
Bloggs	Timothy	T
Bloggs	Rebecca	R
Jones	Bill	Bi
Jones	Bob	Bo

<tbody>
</tbody>

=IF(COUNTIFS(B:B, B16>1,C:C, LEFT(INDEX(C:C, MATCH(C16,C:C,0),0),1)), LEFT(C16, 2), LEFT(C16, 1))


Thanks,

SJ

 

[Robert Mika]

One way would be:
Excel 2010

	A	B	C
1	Surname	Firstname	Initial
2	Bloggs	Timothy	T
3	Bloggs	Rebecca	R
4	Jones	Bill	Bi
5	Jones	Bob	Bo
6

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IF(SUMPRODUCT(--(LEFT($B$2:$B$5,1)=LEFT(B2,1)))=1,LEFT(B2,1),LEFT(B2,2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[sjames1066]

Thanks for the quick reply, although that example results in 2 initials for Timothy?

EDIT: I played around with the formula you gave Robert, and got it to work for single surnames but the duplicates still giving just one initial letter rather than 2

=IF(SUMPRODUCT(--(LEFT(B:B,1)=LEFT(B19,1)))=1,LEFT(C19,2),LEFT(C19,1))

 

[Robert Mika]

Excel 2010
	A	B	C
1	Surname	Firstname	Initial
2	Bloggs	Timothy	T
3	Bloggs	Rebecca	R
4	Jones	Bill	Bi
5	Jones	Bob	Bo
6	tu	tt	t
7	tfa	g	g
8
Sheet1

Cell Formulas
Range	Formula
C2	=IF(SUMPRODUCT(--(LEFT($B$2:$B$10,1)=LEFT(B2,1))*(LEFT($A$2:$A$10,1)=LEFT(A2,1)))=1,LEFT(B2,1),LEFT(B2,2))

 

[Aladin Akyurek]

Thanks for the quick reply, although that example results in 2 initials for Timothy?

EDIT: I played around with the formula you gave Robert, and got it to work for single surnames but the duplicates still giving just one initial letter rather than 2

Needs some tweaking...

D2, just enter and copy down:

=LEFT(C2,1+(SUMPRODUCT(--($B$2:$B$5=B2),--(LEFT($C$2:$C$5)=LEFT(C2)))>1))

 

[Marcelo Branco]

Or maybe

=IF(COUNTIFS(A:A,A2,B:B,LEFT(B2)&"*")=1,LEFT(B2),LEFT(B2,2))

M.

 

[sjames1066]

Re: SOLVED! IF function with multiple criteria including a LEFT and INDEX/MATCH

Thanks so much guys for your time on this!!

Turns out this worked perfectly:

=LEFT(C2,1+(SUMPRODUCT(--($B:$B=B2),--(LEFT($C:$C)=LEFT(C2)))>1))

I can finally go to bed now! Thanks so so much!

 

[Aladin Akyurek]

Re: SOLVED! IF function with multiple criteria including a LEFT and INDEX/MATCH

Thanks so much guys for your time on this!!

Turns out this worked perfectly:

=LEFT(C2,1+(SUMPRODUCT(--($B:$B=B2),--(LEFT($C:$C)=LEFT(C2)))>1))

I can finally go to bed now! Thanks so so much!

You are welcome. I wouldn't want to disturb you sleep, but we need some qualifications. The solution offered won't work when:

Kad, Timothy
Kad, Tiffany
Kad, Titiana

Although such might be rare events, it's better to be aware of.