VBA Prompt for if VLOOKUP finds more than 1 value?

[Tomkat53]

I have a customer list (sorted by their unique email address) that I use vlookup on to lookup the email address then automatically return the company name, address, etc.
However, I now have ONE customer who uses ONE email address for TWO "accounts" (one business / one personal )
Is there any way to use VBA to run "when cell changes" to do a VLOOKUP based on the email address I enter, then PROMPT me (with a dropdown) for which address I want to choose, should that email address return more than 1 match?

 

[jasonb75]

Just those associated with the email address (any number of).

 

[Tomkat53]

Well, I've been unable to get it to work...

I don't quite know where it's failing - could my version of Excel be the issue? (2013)

 

[jasonb75]

You will only be able to use the first formula, the second one will return a #NAME? error in versions of excel older than 2019.

The message that you are seeing usually means that the formula syntax is not correct. It could be a typo but I can't see one in the formula. Do you normally use commas or semicolons in your formulas?

I might need a screen capture showing the formula in excel so that I can see the colour coding of the syntax, this can sometime help to isolate the problem.

 

[Tomkat53]

You will only be able to use the first formula...

I assume you mean this one?

=INDEX(address list, AGGREGATE(15,6,ROW(email list)/(email list=email),scroll wheel value)-MIN(ROW(email list))+1)

 

[jasonb75]

No, the first one in post 9, which is a proper formula based on the table names that you provided.

The one that you have quoted above was a theoretical formula before you provided that information.

 

[Tomkat53]

It works! HUGE Thanks!