Comparing and extracting data from 2 lists

[blue5ky]

I am looking for a formula that will extract data from 1 work sheet for use in another work sheet.

I have 2 lists of names (surnames). List A is an overall list of names. List B is a list containing some of those names but with associated data attached to that name (in 5 columns; D:H). List A has 750 names in it and list B has 150 names.

I need to compare the 2 lists of names and where a name appears in list A and list B copy the data contained in List B columns D:H to the corresponding name in List A.

Any suggestions for a formula?

Thanks

[benishiryo]

hi blue5ky. sounds like a VLOOKUP. i'm just going to teach you how to apply it. Say you have data like these:
List A
Data Range

	A
1	Names
2	Andy
3	Benne
4	Catherine
5	Danny
6	Elaine

List B
Data Range

	A	B	C	D	E	F	G	H
1	Names			D1	D2	D3	D4	D5
2	Benne			2_4	2_5	2_6	2_7	2_8
3	Catherine			3_4	3_5	3_6	3_7	3_8
4	Danny			4_4	4_5	4_6	4_7	4_8

in B2 of List1, try:
=VLOOKUP(A2,Sheet2!A:D,4,0)

that is to look for A2 (Andy) in Sheet2 where List2 is in Column A. I have to range A:D because what I want is in Column D. and you need to tell Excel that Column D is 4 columns away from A. Lastly, put in the zero for exact match. you will get N/A because Andy is not in List2. to hide this error, you can use IFERROR
=IFERROR(VLOOKUP(A2,Sheet2!A:D,4,0),"")

to get Column E would be:
=VLOOKUP(A2,Sheet2!A:E,5,0)

next time though, do upload a sample excel file. then it's easier to customise. i'm not going to, since i already guided you through. To upload, Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
and to illustrate your example better, click on How To Get Quick & Good Answers

[blue5ky]

No luck as yet. The formula seems to just sit there and no value is given. This is the formula in cell E18 (List A)
This is what I actually see in the cell =IFERROR(VLOOKUP(A!D18,B!A2:H2,5,0),"")

I have run Evaluate Formula and the answer given is (#N/A) but this is not shown, the formula is shown. Why does it show the formula and not the answer?