Lookup formula
 

I have 2 worsheets (sheet 1 & sheet 2).
Column B of sheet 2 (Product Name) needs data from sheet 1.
Data in Sheet 1 is column A=Vendor Name , column B = Product Name.
I need to sort through sheet 1 to create a list in sheet 2 that specifies
product name per vendor (i.e. all the products from Pepsi).

I tried the following formula, but it only works if there is 1 product from
Pepsi.
The formula in B2 is =VLOOKUP(A1,Sheet1!B$2:C$1000,2,FALSE).

What formula should I use to sort through sheet 1 to get all the products
from Pepsi? Thank you in advance.

Lookup formula
 

Easiest way here - try a pivot table. Takes only seconds to set it up. It'll
drive out the results you seek, and provide you with "autofilter"
functionalities to boot

Some easy steps to lead you in (xl2003)
In the source table in Sheet1,
Assume you have the col labels "Vendor" in col A, "Product" in col B,
with data in row2 down

Select a cell within the source table
Click Data Pivot table
Click Next Next
In step3 of the wiz., click Layout
Drag n drop Vendor in ROW area
Drag n drop Product in ROW area (below Vendor)
Drag n drop Product in DATA area
(it'll appear as Count of Product)
Click OK Finish. Incredible to believe, but that's it.

Hop over to the pivot sheet (to the left) for the desired results. You can
easily use the pivot "filter" dropdown for "Vendor" to select only a single
vendor to display or any selection of vendors to taste.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rldjda" wrote:
I have 2 worsheets (sheet 1 & sheet 2).
Column B of sheet 2 (Product Name) needs data from sheet 1.
Data in Sheet 1 is column A=Vendor Name , column B = Product Name.
I need to sort through sheet 1 to create a list in sheet 2 that specifies
product name per vendor (i.e. all the products from Pepsi).

I tried the following formula, but it only works if there is 1 product from
Pepsi.
The formula in B2 is =VLOOKUP(A1,Sheet1!B$2:C$1000,2,FALSE).

What formula should I use to sort through sheet 1 to get all the products
from Pepsi? Thank you in advance.

Lookup formula
 

Thanks, but does anyone know how to do this on xl2007?

"Max" wrote:

Easiest way here - try a pivot table. Takes only seconds to set it up. It'll
drive out the results you seek, and provide you with "autofilter"
functionalities to boot

Some easy steps to lead you in (xl2003)
In the source table in Sheet1,
Assume you have the col labels "Vendor" in col A, "Product" in col B,
with data in row2 down

Select a cell within the source table
Click Data Pivot table
Click Next Next
In step3 of the wiz., click Layout
Drag n drop Vendor in ROW area
Drag n drop Product in ROW area (below Vendor)
Drag n drop Product in DATA area
(it'll appear as Count of Product)
Click OK Finish. Incredible to believe, but that's it.

Hop over to the pivot sheet (to the left) for the desired results. You can
easily use the pivot "filter" dropdown for "Vendor" to select only a single
vendor to display or any selection of vendors to taste.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"rldjda" wrote:
I have 2 worsheets (sheet 1 & sheet 2).
Column B of sheet 2 (Product Name) needs data from sheet 1.
Data in Sheet 1 is column A=Vendor Name , column B = Product Name.
I need to sort through sheet 1 to create a list in sheet 2 that specifies
product name per vendor (i.e. all the products from Pepsi).

I tried the following formula, but it only works if there is 1 product from
Pepsi.
The formula in B2 is =VLOOKUP(A1,Sheet1!B$2:C$1000,2,FALSE).

What formula should I use to sort through sheet 1 to get all the products
from Pepsi? Thank you in advance.

Lookup formula
 

Thanks, but does anyone know how to do this on xl2007?

Since it looks increasingly remote that you'd be receiving responses to the
above (I'm sorry that I don't know/have xl2007), suggest you try a fresh new
posting to re-surface it. This time, take care to state at the outset that
you're using xl2007. Good luck!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---