EXCEL vlookup to copy only 2nd instance from sheet 2 to sheet 1

I have a list of client codes and emails on Sheet1 (AC EMAIL), with the client code listed in column A and the email in column B.

I used vlookup to copy the client email to a Sheet2 (AC ADDRESS) containing the client code and their postal address.

I used: =VLOOKUP(A3,'AC EMAIL'!$A$1:$B$25989,2,FALSE)

However some clients appear on Sheet1 2 or 3 times as they have multiple emails listed.

I want to extract just the 2nd and 3rd email onto Sheet2 in another email column.

The formula I used only copied the first email listed, I am having trouble getting the correct formula to show when a second email is given.

This is what I am looking to do, on the single client code row a column showing the first email and a second or third column showing if another email is given.

This has proven to be less straightforward than I had hoped. Appreciate any help.

Solution 1:

This is a great use for a dynamic INDEX MATCH setup using INDIRECT to re-target searched ranges.

Start with the trivial case:

=INDEX(A2:B10,MATCH(D2,A2:A10,0),2)

Given a list of codes and names, go to the (first) row matching code and bring back the name from that row.

But how do you get the second matching record?

Iterate over the list, searching only the rest of it after the first appearance. With MATCH we know where the first matching record appears - so we'll use INDIRECT to repeat the same INDEX MATCH function, but restricting the range to the portion of the list after the first match is found.

In other words, use the same formula but replace A2:B10 with INDIRECT("A"&MATCH(D2,$A$2:$A$10,0)+2&":B10") (then wrap the whole thing with IFERROR for appearance)

Now we have:

=IFERROR(INDEX(INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+2&":B99"),MATCH($D2,INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+2&":A99"),0),2),"not found")

You'll note that this method does rely on the Client Code column being sorted - we have hard coded a +2 into the cell reference that defines the range - remember the original range began with A2, not A1, so we add one row to catch up, then we add another row to start the range from the next row down.

Which should make it pretty clear what we're going to do next:

Just change that +2 to a +3 to start the search range from the NEXT row down (skipping the first two matching instances), to get the third instance for the next column over.

But what if we have a lot of columns, or just don't like updating formulas?

Finally, we'll replace those +2 and +3 offsets with cell references too, using COLUMN. This makes the formula extensible horizontally without any manual edits when copying over:

=IFERROR(INDEX(INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+COLUMN()-4&":B99"),MATCH($D2,INDIRECT("A"&MATCH($D2,$A$2:$A$99,0)+COLUMN()-4&":A99"),0),2),"not found")

Note that because I have my example destination data starting in cell E2, my COLUMN offset does need to be set manually, once, to COLUMN()-4 - but once it is, that formula can be written in the top left cell of your destination range and copied down and across for as many instances as you need (again, as long as the source data is sorted by code, since the whole premise of the iterative INDEX MATCH is to repeat the search from the 'next row down').