Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restrictions on size of XL file? | Setting up and Configuration of Excel | |||
Connect columns, with restrictions | Excel Worksheet Functions | |||
character restrictions when importing data from a text file | Excel Discussion (Misc queries) | |||
Cannot access internet from taskpane due to computer restrictions | Excel Discussion (Misc queries) | |||
Subtract time and dates with restrictions | Excel Worksheet Functions |