# Can Help With an =INDEX

December 23, 2012 at 23:20:17
Specs: Windows 7
 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;sI need it to return the first matchHappy xmasThanks

See More: Can Help With an =INDEX

#1
December 24, 2012 at 00:20:58
 Hi EmoryWhat is it you want "returned" and where? Do you want, for each entry in A, the relative position in L or M?

Report •

#2
December 24, 2012 at 00:28:40
 Hi ThanksIf it finds a match in L Or M return the match from col A into Col BHappy Xmas

Report •

#3
December 24, 2012 at 00:48:35
 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 ```

Report •

Related Solutions

#4
December 24, 2012 at 01:07:25
 Hi ThanksIn 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 columnsThanks

Report •

#5
December 24, 2012 at 01:35:01
 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 ```

Report •

#6
December 24, 2012 at 04:12:47
 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

Report •

#7
December 24, 2012 at 06:20:32
 It is now. Did you try solution in Post #5?Merry Christmas to you too.

Report •

#8
December 27, 2012 at 07:07:14
 Hope u had a good xmas dayThe 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

Report •

#9
December 27, 2012 at 07:24:25
 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)))

Report •

#10
December 27, 2012 at 10:49:08
 Hi EmoryHope 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 havenames in Cols D and Eemail addresses in F and G - apparently 2 email addresses per namenames of the people emailed in Col IYou wantto 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 GTo 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 Doe ```If 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?

Report •

#11
December 28, 2012 at 01:56:57
 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 workMany many thanks

Report •

#12
December 28, 2012 at 02:38:52
 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 haveVLOOKUP(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.

Report •

#13
December 28, 2012 at 03:55:50
 F G H Iemail 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 deleted fails becuase it must have bounced so we emailed out on the office email email address deletedDoes that help sorry its a painThanks so muchedited by moderator: email addresses deleted

Report •

#14
December 28, 2012 at 04:25:53
 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.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#15
December 28, 2012 at 04:29:16
 Sorry Sorry Sorry Sorry

Report •

#16
December 28, 2012 at 11:21:34
 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.bilgoody2shoes@nice.guyIf you continue to post real email addresses in this fourm you run the risk of having your account suspended.DerbyDad03Offiice Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#17
December 28, 2012 at 11:58:54
 EmoryI'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.

Report •

#18
December 29, 2012 at 00:53:28
 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

Report •