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;s

I need it to return the first match

Happy xmas

Thanks


See More: Can Help With an =INDEX

Report •


#1
December 24, 2012 at 00:20:58
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?


Report •

#2
December 24, 2012 at 00:28:40
Hi Thanks

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

Happy 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 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


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 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


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 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 I

You 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 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 work

Many 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 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.


Report •

#13
December 28, 2012 at 03:55:50
F G H I

email addresses deleted

Dont 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 deleted

Does that help sorry its a pain

Thanks so much

edited 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.

DerbyDad03
Office Forum Moderator

Click 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.bil
goody2shoes@nice.guy

If 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.


Report •

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


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 •


Ask Question