How to do look up with restrictions
 

I am trying to create a lookup on another workbook, I want it to look up
items under certain categories. I currenlty have the following
A b c
Carrots Produce $1.00
Wrap Paper $.15

Etc.

Colum A is the item I want to look up, but I only want it to show items
under one category i.e. Paper.

Is this possible, if so anyone have any ideas or a formula that would work?
Any help would be apprecited.


Thanks,

How to do look up with restrictions
 

Since column C is numeric, SUMPRODUCT will work
=SUMPRODUCT(--(A1:A6="Carrots"),--(B1:B6="Paper"),C1:C6)
Or, for another sheet
=SUMPRODUCT(--(Sheet2!A1:A6="Carrots"),--(Sheet2!B1:B6="Paper"),Sheet2!C1:C6)
I am assuming there is only on Carrots/Paper record in the table
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JackR" wrote in message
...
I am trying to create a lookup on another workbook, I want it to look up
items under certain categories. I currenlty have the following
A b c
Carrots Produce $1.00
Wrap Paper $.15

Etc.

Colum A is the item I want to look up, but I only want it to show items
under one category i.e. Paper.

Is this possible, if so anyone have any ideas or a formula that would
work?
Any help would be apprecited.


Thanks,

How to do look up with restrictions
 

I dont think I got my point across correctly, I have sevral categories,
paper, produce, poultry, frozen etc. and accordingly items in another colums,
prices in another column, what I want is a vlookup, that will only show say
products under paper category, and then be able to give me the price for the
given item in the paper category.

Does this make sense. Aince I have to have all my items in one sheet, with
each item having a different category.

"Bernard Liengme" wrote:

Since column C is numeric, SUMPRODUCT will work
=SUMPRODUCT(--(A1:A6="Carrots"),--(B1:B6="Paper"),C1:C6)
Or, for another sheet
=SUMPRODUCT(--(Sheet2!A1:A6="Carrots"),--(Sheet2!B1:B6="Paper"),Sheet2!C1:C6)
I am assuming there is only on Carrots/Paper record in the table
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JackR" wrote in message
...
I am trying to create a lookup on another workbook, I want it to look up
items under certain categories. I currenlty have the following
A b c
Carrots Produce $1.00
Wrap Paper $.15

Etc.

Colum A is the item I want to look up, but I only want it to show items
under one category i.e. Paper.

Is this possible, if so anyone have any ideas or a formula that would
work?
Any help would be apprecited.


Thanks,

How to do look up with restrictions
 

There is an Excel feature called auto-filter. Data Filter Autofilter

You can separately filter any of the columns. For example, if you select
Produce, then only the "Produce" rows will appear and all the "Produce" rows
will appear
--
Gary's Student


"JackR" wrote:

I dont think I got my point across correctly, I have sevral categories,
paper, produce, poultry, frozen etc. and accordingly items in another colums,
prices in another column, what I want is a vlookup, that will only show say
products under paper category, and then be able to give me the price for the
given item in the paper category.

Does this make sense. Aince I have to have all my items in one sheet, with
each item having a different category.

"Bernard Liengme" wrote:

Since column C is numeric, SUMPRODUCT will work
=SUMPRODUCT(--(A1:A6="Carrots"),--(B1:B6="Paper"),C1:C6)
Or, for another sheet
=SUMPRODUCT(--(Sheet2!A1:A6="Carrots"),--(Sheet2!B1:B6="Paper"),Sheet2!C1:C6)
I am assuming there is only on Carrots/Paper record in the table
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JackR" wrote in message
...
I am trying to create a lookup on another workbook, I want it to look up
items under certain categories. I currenlty have the following
A b c
Carrots Produce $1.00
Wrap Paper $.15

Etc.

Colum A is the item I want to look up, but I only want it to show items
under one category i.e. Paper.

Is this possible, if so anyone have any ideas or a formula that would
work?
Any help would be apprecited.


Thanks,