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

LouisDickey

New Member
Joined
Apr 18, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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,"")),"")
 
Upvote 0
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))),"")
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHI
1emailNameUsing Filter Function
2thesmith@gmail,comMr John SmithMr John SmithMrs Jane SmithMiss Janet Smith
3testemail@att,netJohn TestJohn TestJoye Test
4thesmith@gmail,comMrs Jane SmithMr John SmithMrs Jane SmithMiss Janet Smith
5testemail@att,netJoye TestJohn TestJoye Test
6thesmith@gmail,comMiss Janet SmithMr John SmithMrs Jane SmithMiss Janet Smith
7jones@gmail,comSam JonesSam Jones
8
9
10emailNameUsing Index
11thesmith@gmail,comMr John SmithMr John SmithMrs Jane SmithMiss Janet Smith
12testemail@att,netJohn TestJohn TestJoye Test 
13thesmith@gmail,comMrs Jane SmithMr John SmithMrs Jane SmithMiss Janet Smith
14testemail@att,netJoye TestJohn TestJoye Test 
15thesmith@gmail,comMiss Janet SmithMr John SmithMrs Jane SmithMiss Janet Smith
16jones@gmail,comSam JonesSam Jones  
Sheet1
Cell Formulas
RangeFormula
G2:I2,G7,G6:I6,G5:H5,G4:I4,G3:H3G2=TRANSPOSE(FILTER($C$2:$C$7,B2=$B$2:$B$7,""))
G11:I16G11=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.
 
Upvote 0

Forum statistics

Threads
1,224,352
Messages
6,178,069
Members
452,822
Latest member
MtC

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top