Searching a column on 1 sheet for matches on another

[jhetfield]

I have worksheet 1 with 5,000 part #s & their corresponding prices. I entered 1,000 of those #s on worksheet 2 (they can't be in ascending order (I.E 12,10,25,11).

Now I want to match these 1,000 part #s on worksheet 2 to the ones on worksheet 1 & input the prices that correspond to each part #. This way all I have to do is key in the part #s on worksheet 2 & the correct prices from worksheet 1 automatically are input.

Here's a layman’s way of writing the logical equivalent.

If cell in column A on worksheet 1 matches cell in column A on worksheet 2, than the cell adjacent column A on worksheet 2 equals the cell adjacent column A on worksheet 1.

Remember that while the part #s with the pricing on worksheet one (5,000 total) are in ascending order, the part #s that need the corresponding pricing I entered on worksheet two (1,000 total) can't be in any kind of order.


I'm a novice compared to most of the folks in this forum, If you could provide the string I need to use, that would be much appreciated.

Regards, Jason

 

[Zathras]

Look in the help file for the worksheet function VLOOKUP and follow the examples.

If you can't get it to work the way you want, post again with details of what you tried and someone can assist you.

 

[mmtraining]

Put the cursor in A2 on worksheet 2 and put in the following function:

=vlookup(A1,NameOfWorksheet1!$A$1:$B$5000,2,FALSE)

Split down, this means:

=vlookup(

Do a vertical search

A1,

To find the contents of A1

NameOfWorksheet1!$A$1:$B$5000,

on Worksheet 1 in the fixed range a1 to B5000

2,

when found, take the information out of column 2

FALSE)

and search for an exact match, however the lists are sorted.

If this works well, drag the function from b2 down to the bottom of the list and feel happy about time saved.

All the best,

Carol, Berlin

 

[Dreamboat]

Download MyVlookup for a sample on how to do just this type of thing:

http://www.theofficeexperts.com/downloads.htm

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX