Lookup & Match?
Posted by Jennifer Ann on March 09, 2001 7:02 AM
On sheet 1, column A lists over 200 company names, column B the commodity of each company. I need a formula that, when I enter (paste, actually) a company name on sheet 2, column A, will look for that company name on sheet 1, and if it finds a match, enter the oppropriate commodity from sheet 1, column B into sheet 2, column B. A report will generate a list of over 400 company names that I can copy and paste into sheet 2, and at this point I still have to manually search for each company name and enter any matching commodity listing. Those without matching commodities are moved to another sheet.
Thanks for any help!
Posted by Mark W. on March 09, 2001 7:57 AM
Jennifer, sounds like VLOOKUP() will do the trick.
Do you need more pointers than this?
Posted by Dave Hawley on March 09, 2001 2:48 PM
Hi jennifer
Marks most likely right, VLOOKUP is pobably hat you want. Here is how you would use it in your case:
=VLOOKUP(B1,Sheet1!A:B,2,FALSE)
Where "B1" contains the Company name to look for.
"Sheet1!A:B" is the table of Company names and commodities.
"2" is telling VLOOKUP to find a match on the same row as the Company, but over 1 Column (B).
"False" means find an exact match.
As a tip for looking up your Company names try this:
1. Higlight Column A (Company Names) and name it "CoNames"
2. On your Lookup sheet select cell B1 (or any cell) and go to Data>Validation.
3. Select "List" from the "Allow" box. Ensure "Ingnore blanks" and "In cell drop down" are checked.
4. In the "Source" box type: =CoNames then click OK.
Now you can simply select the company name you want to return the Commodity for and have your VLOOKUP reference that cell.
Dave
OzGrid Business Applications
Posted by Jennifer Ann on March 10, 2001 8:35 AM
Thank you ever so much! This is fantastic!
