comparing lists & completing rows

G

Guest

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

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top