Can anybody help so I can put this to bed before xmas day "=INDEX(A2:A2500,MATCH(A2,L2:M2500))

There are 1712 email address's in Col A apprearing somewhere in Cols L&M where there are 5,000 email address;s

I need it to return the first match

Happy xmas

Thanks

Hi Emory What is it you want "returned" and where? Do you want, for each entry in A, the relative position in L or M?

Hi Thanks If it finds a match in L Or M return the match from col A into Col B

Happy Xmas

While you were answering my question I was putting together a different sort of solution - this one shows where there is a match the row in L or M where the match was found. Seems a bit more useful than just returning what already is in A. If you don;t like it I'll change it. =IF(AND(ISNA(MATCH(A6,$L$2:$L$2500,0)),ISNA(MATCH(A6,$M$2:$M$2500,0))),"",IF(ISNA(MATCH(A6,$L$2:$L$2500,0)),"M"&(MATCH(A6,$M$2:$M$2500,0)+1),"L"&MATCH(A6,$L$2:$L$2500,0)+1))

Returns these results

Address1 Address5 Address6 Address2 Address14 Address3 Address4 Address11 Address5 L2 Address14 Address6 M2 Address15 Address7 Address16 Address16 Address8 Address17 Address17 Address9 Address10 Address11 M5 Address12 Address13 Address14 L3 Address15 L7 Address16 L8 Address17 L9 Address18 Address19 Address20

Hi Thanks In col A I have 1700 email address's I need to find the same email address in Col K and L and return that email address I can match the name and address using Vlook up but cant get it to do same thing with the email address becuase they are two columns

Thanks

My preference is VLOOKUP and I used it here. See if it gives you what you want. When DerbyDad gets back he'll have an ingenious way to do this.

=IF(AND(ISNA(VLOOKUP(A2,$K$2:$K$2500,1,FALSE)),ISNA(VLOOKUP(A2,$L$2:$L$2500,1,FALSE))),"",IF(ISNA(VLOOKUP(A2,$K$2:$K$2500,1,FALSE)),VLOOKUP(A2,$L$2:$L$2500,1,FALSE),VLOOKUP(A2,$K$2:$K$2500,1,FALSE)))

Address1 Address5 Address6 Address2 Address3 Address4 Address11 Address5 Address5 Address14 Address6 Address6 Address15 Address7 Address16 Address8 Address17 Address9 Address10 Address11 Address11 Address12 Address13 Address14 Address14 Address15 Address15 Address16 Address16 Address17 Address17 Address18 Address19 Address20 Address21 Address22 Address23 Address24 Address25

I have posted the formula it works fine how do I get to return the actual value rather than the column and cell number Is it xmas day where u are

Many thanks

It is now. Did you try solution in Post #5? Merry Christmas to you too.

Hope u had a good xmas day The formula worked fine except I got it the wrong way round

I have two columns of email address's in F and G, who the people we have on our database. In Column "I" I have a list of people we have emailed I need to return the email address to Col H which matches to the email address in either F or G where the persons Title,Name,and Surname appears in Column C,D,E

"=IF(AND(ISNA(VLOOKUP($F$2:$F$2500,I7,1,FALSE)),ISNA(VLOOKUP($G$2:$G$2500,I7,1,FALSE))),"",IF(ISNA(VLOOKUP($F$2:$F$2500,I7,1,FALSE)),VLOOKUP($G$2:$G$2500,1,FALSE),VLOOKUP($F$2:$F$2500,I7,1,FALSE)))

Thanks very much

Yes Post 5 worked but I had it the wrong way round "=IF(AND(ISNA(VLOOKUP($F$2:$F$2500,I7,1,FALSE)),ISNA(VLOOKUP($G$2:$G$2500,I7,1,FALSE))),"",IF(ISNA(VLOOKUP($F$2:$F$2500,I7,1,FALSE)),VLOOKUP($G$2:$G$2500,1,FALSE),VLOOKUP($F$2:$F$2500,I7,1,FALSE)))

Hi Emory Hope you had a good xmas day as well.

Is this solved? I can't see how the formula you posted would work. If it is not solved I think you'll need to post a sample of your data, As I understand it

You have

names in Cols D and E

email addresses in F and G - apparently 2 email addresses per name

names of the people emailed in Col IYou want

to match the names of people emailed (Col I)

with names in your data base (Cols D and E)

And return the email addresses for people Emailed (Col I)

From the list of email addresses in Col F or G

To Col H.Something like this

C D E F G H I Title Name Surname email1 email2 ????? Person emailed Mr John Doe Jdoe@com.com Jdoe@com.org John DoeIf that is the case then the challenge is to match what you have in Col I with what you have in Cols D and E and return what is in F or G to H.

Is that correct?

Yes thats right the 1st emal address is their personel email address and the second is the office email adderess. We have emailed out using the personel email 1st and if that bounces back we used the office email. So I need to match the email address that is on Dot Mailer which is in Col I with their name. Hope that makes sence. The formula I used works on the 1st Line then all others in the list are blanks so it doesnt work Many many thanks

No I don't see what you are trying to match and what you are trying to get. I can see that your formula is not right. Where you have

VLOOKUP($F$2:$F$2500,

I7,1,FALSE)You should probably have

VLOOKUP(

I7,$F$2:$F$2500,1,FALSE)Try fixing that for each of the vlookups in the formula and let me know if that works.

If not, could you list out each column you have and an example of each column and which columns you want to match.

F G H I

email addresses deletedDont know if this will paste correcly but in F is the direct email address G is office email in H is the email found in Col I (which for the sake of expample I checked via "Shift F5" manually) you will see that

email address deletedfails becuase it must have bounced so we emailed out on the office email

email address deletedDoes that help sorry its a pain

Thanks so much

edited by moderator: email addresses deleted

Emory, I have deleted the email addresses from your post. I don't know if they were real or not but they looked real enough to concern me.

You should never post email address in a public forum such as computing.net. We do not want to become known as a site where email addresses can be harvested. Once the bad guys start hanging around, we will be open to all sorts of security issues.

DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Sorry Sorry Sorry Sorry

Why did you post the email addresses again after I specifically said that you should not? I checked some of the email addresses and they appear to be valid.

Would you like it if someone posted your email address on random sites without your permission? Would you like to put up with the resulting spam that that action might generate?

Posting real email addresses in this forum is not going to result in a better answer to your question. Use fake data as examples. If you feel that the email address format is important to your question, then use fake email addresses:

daffy_duck@quack.bil

goody2shoes@nice.guyIf you continue to post real email addresses in this fourm you run the risk of having your account suspended.

DerbyDad03

Offiice Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Emory I'd really like to help but your descriptions are too vague and seemingly contradictory.

Can you post an example in the format I showed you in Post #10 with a detailed explanation of each of the columns e.g. and which ones you want to match and what your expected results are. And as DerbyDad has warned, do not use real email addresses.

I thought wrongly using Click Here Before Posting Data or VBA Code ---> How To Post Data or Code. Covered the data Got it 100% now wont happen again no need to suspend me from school

Thanks

Ask Your Question

Weekly Poll

Do you think Microsoft should invest in Cyanogen?

Discuss in The Lounge

Poll History