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

sjames1066

New Member
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

Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Robert Mika

MrExcel MVP
One way would be:
Excel 2010
ABC
1SurnameFirstnameInitial
2BloggsTimothyT
3BloggsRebeccaR
4JonesBillBi
5JonesBobBo
6

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C2=IF(SUMPRODUCT(--(LEFT(\$B\$2:\$B\$5,1)=LEFT(B2,1)))=1,LEFT(B2,1),LEFT(B2,2))

</tbody>

<tbody>
</tbody>

sjames1066

New Member
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))

Last edited:

Robert Mika

MrExcel MVP

Excel 2010
ABC
1SurnameFirstnameInitial
2BloggsTimothyT
3BloggsRebeccaR
4JonesBillBi
5JonesBobBo
6tuttt
7tfagg
8
Sheet1
Cell Formulas
RangeFormula
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))

MrExcel MVP
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

MrExcel MVP
Or maybe

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

M.

sjames1066

New Member
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!

MrExcel MVP
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:

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

Replies
3
Views
390
Replies
3
Views
172
Replies
12
Views
942
Replies
7
Views
497
Replies
1
Views
253

1,195,667
Messages
6,011,052
Members
441,580
Latest member
BornholmerBjarne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back