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?

 

[Tomkat53]

HELP??? ANYONE??? I know enough to "kinda" understand the procedure of how to make this work, but I don't know the EXACT code, and unfortunately I can't spend hours experimenting, trying to figure it out from scratch.

 

[steve the fish]

The reason you have had no replies is you have given us nothing to work on. When cell changes? Which cell? Where is the lookup value? Where is the lookup table? For a dropdown i believe you will first need to create a userform to host a combobox. Then you dont say how that address is used. As for your follow up its impossible to give you code for reasons above.

 

[Tomkat53]

Sorry, hopefully this will help...

Whenever I paste an email address into D12 (where "TEST EMAIL" is now)
Then D13 performs a lookup on the 2nd sheet (Customer-Company List) and returns COMPANY, ADDRESS1, CITY, STATE, ZIP etc.
It works fine, as of now, because everyone has ONE unique email address that returns one row of company information.
However, I now have a customer who uses ONE email address... for TWO accounts (business and personal)
What I would like to do via VBA is...
Whenever I paste this person's email into D12 - to have a prompt appear allowing me to choose WHICH account to use (business or personal) because vlookup will only return one of the two. (the vlookup vba criteria can either be by this person's exact email, or have it trigger based on the fact that it finds 2 results, whichever is easiest)

I hope that I'm explaining this clearly enough. It seems straight-forward enough in my head, but I know that's not always the case.

 

[jasonb75]

How about a scroll wheel (or a cell that you can enter a number into) and no vba?

Changing a formula like

=VLOOKUP(email,table, column,false)

to one like

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

should do the trick if this method is acceptable.

Depending on the exact method used, you might need to use vba to reset the scroll wheel / number cell back to a default of 1 whenever the email address is changed.

 

[jasonb75]

How about a scroll wheel

To late to edit my previous post, correct name for it is 'Spin Button'

Also, vba would not be needed to reset it back to 1, it would be preferable (in my opinion) but the formula could be tweaked to work without doing so.

 

[Tomkat53]

How about a scroll wheel (or a cell that you can enter a number into) and no vba?

Changing a formula like

=VLOOKUP(email,table, column,false)

to one like

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

should do the trick if this method is acceptable.

Depending on the exact method used, you might need to use vba to reset the scroll wheel / number cell back to a default of 1 whenever the email address is changed.

I tried this, but it didn't work... probably because I don't know where the reference to the value I enter in D12 goes.

=INDEX(Table1[ADDRESS 1], AGGREGATE(15,6,"Table1[EMAIL]=Beth Riccardo <Briccardo@Gearhartherr.com>",1)-MIN(ROW(Table1[EMAIL]))+1)

 

[Tomkat53]

I also don't know where to choose the proper arrays, columns, etc
Here's some sample data from Sheet 2 (Customer-Company List)

Obviously, the duplicate EMAIL is what's causing my issue. I wish I could just tell the customer to use 2 separate emails, but that's not an option.

 

[jasonb75]

Try

=INDEX(Table1[ADDRESS 1], AGGREGATE(15,6,ROW(Table1[EMAIL])/(Table1[EMAIL]=D12),MOD(M1-1,COUNTIF(Table1[EMAIL],D12))+1)-ROW(Table1[[#Headers],[EMAIL]])))

Then add a spin button to your sheet from Developer >Insert > Form Controls > Spin Button (Form Control).

Right click the spin button, then Format Control and enter =$M$1 into the 'Cell Link' box and change the Minimum Value to 1.

As you scroll the spin button it should cycle through the addresses if done correctly.

Noticing your latest reply, it might be necessary to include the textjoin function as part of the formula, something like.

=TEXTJOIN(CHAR(10),1,INDEX(Table1[[ADDRESS 1]:[PHONE]], AGGREGATE(15,6,ROW(Table1[EMAIL])/(Table1[EMAIL]=D12),MOD(M1-1,COUNTIF(Table1[EMAIL],D12))+1)-ROW(Table1[[#Headers],[EMAIL]])),0))

 

[Tomkat53]

Scroll through ALL the company addresses? (I have over 150 in my actual list), or just the 2 associated with the email address that I enter?