Using INDEX...SMALL...IF...ROW and COUTNIF in order to match multiple peoples names to single email for mail marge

[LouisDickey]

Hi there,

I have found some old posts on this topic but nothing that seems to solve my issue.

I am using the below formula in order to pull a list of individuals names that have correspondence through the same email address. For example if Mr John Smith, Mrs Jane Smith and Miss Janet Smith are all separate entries but all use the same email address i.e. thesmiths@gmail.com and I have a list (col B) of the email address used and a list of individuals (col c) which are aligned correctly. This means that there are three entries of thesmiths@gmail.com in col B but for the purpose of the mail merge I am using I want to include them (and other information) in a single email. So I am looking to get an output in G2, H2, I2 etc. of these names where the value in F2 is the relevant email address. I have used the UNIQUE function to effectively remove duplicates from the raw data in col B in col F. For completeness col E has the name linked with the email address.

=IFERROR(INDEX($C$2:$C$144, SMALL(IF($B$2:$B$144=F2,ROW($B$2:$B$144)-ROW($B$2)+1), COUNTIF($F2:F2,F2))),"")

I originally had this formula which is identical apart from I moved to using a COUNTIF from a COLUMN function as with the below the first time the email occurred in col B it was just not pulling, i.e. with the below Mrs Jane Smith and Miss Janet Smith would be in G2 and H2 respectively but I want Mr John Smith in G2 and Jane and Janet in H2 and I2.

=IFERROR(INDEX($C$2:$C$144, SMALL(IF($F2=$B$2:$B$144,ROW($B$2:$B$144)-ROW($B$2)+1), COLUMN(B1))),"")

I hope I have gone into enough detail in order to get some help.

Any input would be appreciated.

 

[excel_learnerz]

It looks like the issue with your formula is that it is using the COUNTIF function with a range that includes the entire column F, which means it is counting all instances of the email address, not just the ones that match the current row. This is causing the INDEX function to return the wrong values.

To fix this, you can modify your formula to use a relative reference for the range in the COUNTIF function. Here is the updated formula:

=IFERROR(INDEX($C$2:$C$144, SMALL(IF($B$2:$B$144=F2,ROW($B$2:$B$144)-ROW($B$2)+1), COUNTIF($F$2:F2,F2))),"")

Note that I changed the range in the COUNTIF function to be relative to the current row, by using F2:F2 instead of F2:F. This ensures that the COUNTIF function only counts the email addresses that match the current row.

With this modification, the formula should return the correct names for each email address, in the order they appear in the data. If you want to combine the names into a single cell, you can use a text join function like CONCAT or TEXTJOIN. For example:

=IFERROR(CONCAT(IF($B$2:$B$144=F2,$C$2:$C$144&", ","")),"")

This formula uses the CONCAT function to join together all the names that match the current email address, separated by commas. Note that this formula assumes that you are using a version of Excel that supports the CONCAT function. If not, you can use the TEXTJOIN function instead, like this:

=IFERROR(TEXTJOIN(", ",TRUE,IF($B$2:$B$144=F2,$C$2:$C$144,"")),"")

 

[LouisDickey]

Thank you for the help.

I am still having issues when trying to drag this formula across columns, I need the names to be in individual cells as I am then using a lookup on the names to pull other data.

So when I pull this across from G2 to H2 it becomes the below formula, for whatever reason absolute references and the like always feel like a bit of a minefield to me. How to I correct the formula to mean that if I drag this across it will then find the next person who has that same email. i.e. G2 has John Smith and I need H2 to have Jane Smith and I2 to have Janet Smith. I then use a lookup to G2, H2...to form part of the mail marge.

=IFERROR(INDEX($C$2:$C$144, SMALL(IF($B$2:$B$144=G2,ROW($B$2:$B$144)-ROW($B$2)+1), COUNTIF($F$2:G2,G2))),"")

 

[excel_learnerz]

I've adjusted the formula so that it can be dragged across columns to return the names in separate cells:

=IFERROR(INDEX($C$2:$C$144, SMALL(IF($B$2:$B$144=$F2, ROW($B$2:$B$144)-ROW($B$2)+1), COLUMN()-COLUMN($G$1))),"")

Please enter this formula in cell G2 and drag it across columns to get the names associated with the email addresses.

This formula uses the COLUMN()-COLUMN($G$1) part to determine which name to return based on the current column. The COLUMN function returns the current column number, so as you drag the formula across columns, it will increment the value, effectively returning the next name associated with the email address.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[LouisDickey]

Thanks for all your help, seems to be working, for some reason it is skipping col G so the first name is going in col H but this is fine as the following names are coming afterwards.

I would post sample data but due to the nature of the data it would require some work to make sure it did not contain certain information and I was planning to do this if the solution did not seem to be possible without the data.

 

[AhoyNC]

With Excel 365 you should be able to do this with the Filter function.
Or with INDEX take a look at the formula in cell G11 in the example. Drag this formula down and across.
Change ranges to match your actual data.

Book1
	A	B	C	D	E	F	G	H	I
1		email	Name				Using Filter Function
2		thesmith@gmail,com	Mr John Smith				Mr John Smith	Mrs Jane Smith	Miss Janet Smith
3		testemail@att,net	John Test				John Test	Joye Test
4		thesmith@gmail,com	Mrs Jane Smith				Mr John Smith	Mrs Jane Smith	Miss Janet Smith
5		testemail@att,net	Joye Test				John Test	Joye Test
6		thesmith@gmail,com	Miss Janet Smith				Mr John Smith	Mrs Jane Smith	Miss Janet Smith
7		jones@gmail,com	Sam Jones				Sam Jones
8
9
10		email	Name				Using Index
11		thesmith@gmail,com	Mr John Smith				Mr John Smith	Mrs Jane Smith	Miss Janet Smith
12		testemail@att,net	John Test				John Test	Joye Test
13		thesmith@gmail,com	Mrs Jane Smith				Mr John Smith	Mrs Jane Smith	Miss Janet Smith
14		testemail@att,net	Joye Test				John Test	Joye Test
15		thesmith@gmail,com	Miss Janet Smith				Mr John Smith	Mrs Jane Smith	Miss Janet Smith
16		jones@gmail,com	Sam Jones				Sam Jones
Sheet1

Cell Formulas
Range	Formula
G2:I2,G7,G6:I6,G5:H5,G4:I4,G3:H3	G2	=TRANSPOSE(FILTER($C$2:$C$7,B2=$B$2:$B$7,""))
G11:I16	G11	=IFERROR(INDEX($C$11:$C$16,AGGREGATE(15,6,(ROW($C$11:$C$16)-ROW($C$11)+1)/($B11=$B$11:$B$16),COLUMNS($G$11:G11))),"")
Dynamic array formulas.