comparing lists & completing rows

Feb 26th, 2002, 05:37 PM

I am quite new to Excel. I have a small list of names, and another list that contains many names, billing numbers, etc. I am wondering - Is possible to compare the names in the two lists and copy the matching data from the large list to the small list?

Derek · Feb 26th, 2002, 06:58 PM

Hi
The formulas that can help you are VLOOKUP and MATCH. VLOOKUP is probably quite suitable, with some reservations - your large list needs to be sorted into sequential order (numeric or alphabetical) as the formula searches down the list from top to bottom trying to find a match.

Say your small list has names in A1:A10
Say your big list (array) has names in D1:D100 and data in E1:E100.

To find the data for your name in A1, type this formula in B1 =VLOOKUP(A1,$D$1:$E$100,2,FALSE). You can then scroll this formula down column B.

The array D1:E100 has $ signs to fix the columns and rows so that they do not change when you scroll the formula down.

The 2 in the formula refers to the 2nd column of your array (the column with the data you want to pick up)

If you do not add the "False" this formula is likely to give you the nearest answer if there is no exact match. Adding "False" to the formula makes it return an error value if the name you are searching for is not in the list.

Look in the help section for more examples.
Hope this helps
Regards
Derek

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 26th, 2002, 05:37 PM	#1
Guest Posts: n/a	I am quite new to Excel. I have a small list of names, and another list that contains many names, billing numbers, etc. I am wondering - Is possible to compare the names in the two lists and copy the matching data from the large list to the small list?

Feb 26th, 2002, 06:58 PM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi The formulas that can help you are VLOOKUP and MATCH. VLOOKUP is probably quite suitable, with some reservations - your large list needs to be sorted into sequential order (numeric or alphabetical) as the formula searches down the list from top to bottom trying to find a match. Say your small list has names in A1:A10 Say your big list (array) has names in D1:D100 and data in E1:E100. To find the data for your name in A1, type this formula in B1 =VLOOKUP(A1,$D$1:$E$100,2,FALSE). You can then scroll this formula down column B. The array D1:E100 has $ signs to fix the columns and rows so that they do not change when you scroll the formula down. The 2 in the formula refers to the 2nd column of your array (the column with the data you want to pick up) If you do not add the "False" this formula is likely to give you the nearest answer if there is no exact match. Adding "False" to the formula makes it return an error value if the name you are searching for is not in the list. Look in the help section for more examples. Hope this helps Regards Derek