Simple lookup and match issue

[rolando87]

Hi Guys,

I hope one of you can help, my problem seems so simple but for some reason struggling.

I have 2 sets of data (of different row lengths) that I need to match up, so that I can match the client number in column A with the appropriate email found in a separate dataset that doesn't have the same amount of rows since not all clients provided emails.

For example

Data set 1 (maybe 500 rows)
Column A = a 3 digit client number
Columns B-D = clients data like address, phone, etc EXCEPT email address

Dataset 2 (maybe 300 rows since not all clients have emails)
Column F = email
Column G = the same 3 digit client numbers found in column A.

what formula do I need to use to basically say ok column A in dataset 1 with this client number has this email

Thanks for any help or clues!

 

[kweaver]

A sample of your data and the solution(s) expected would help. Please use XL2BB.

 

[kweaver]

Or, if I understand you correctly, how about this?

Book2
	A	B	C	D
1	290
2	430			xxx@yahoo.com
3	931
4	164
5	910			yyy@yahoo.com
6	459
7	766
8	293
9	888			rolando@gmail.com
10	944
DataSet1

Cell Formulas
Range	Formula
D1:D10	D1	=IFERROR(INDEX(DataSet2!$F$1:$F$500,MATCH(DataSet1!A1,DataSet2!$G$1:$G$500,0),1),"")

290​
430​			xxx@yahoo.com
931​
164​
910​			yyy@yahoo.com
459​
766​
293​
888​			rolando@gmail.com
944​

 

[rolando87]

Column A (client number)	Column B	Column F	Column G (client Number
111		hello@test.com	222
222		hi@hi.com	555
333		test@test.com	333
444		123@hello.com	111
555
666

Thanks for your feedback,

There is a quick example

So I need to populate Column B with the emails found in column F that match the right client numbers. So cell B1 should say 123@hello.com for example.

I hope that is clear

 

[rolando87]

Ah ok, the code you provided does solve the issue, I was getting SPILL error but realized if I just do it only on cell 1 it populates all 1000 cells or however many rows I enter.

Thanks very much!

 

[kweaver]

Your profile says Excel 2010...shouldn't have a spill issue with 2010.

 

[jtakw]

Hi,

Another way:

Book3.xlsx
	A	B	C	D	E	F	G
1	ID	email
2	290						459
3	430	xyz@abc.net				abc@xyz.com	766
4	931						293
5	164					123@def.com	888
6	910						944
7	459						290
8	766	abc@xyz.com				xyz@abc.net	430
9	293						931
10	888	123@def.com					164
11	944						910
Sheet918

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(LOOKUP(2,1/(FIND(A2,G$2:G$20)*(F$2:F$20<>"")),F$2:F$20),"")