Which Function(s) do I use?
 

Does anyone know which function(s) I can use to pull certain names from a
list that meet either of two criteria? Below is list of all products
available: I only want to pull products and corresponding data that either
do not have "sub-product" (i.e. Product A,C,D) or is labled "sub-product;1"
(i.e. Product B;1, Product E;1 Product F;1).

Product Name Total Sales
Product A 5
Product B;1 10
Product B;2 15
Product B;3 2
Product C 50
Product D 20
Product E;1 25
Product E;2 10
Product E;3 31
Product F;1 34
Product F;2 20
Product G 50
Product H 34

Assuming
1) your Product table is a 2 column named range called Products
2) Column A contains the value you want to look up in the table.

=IF(ISNA(VLOOKUP(B2,Products,2,FALSE)),0,VLOOKUP(B 2,Products,2,FALSE))
If B2 = Product A, the formula returns 5
If B2 = Product Z (not in the list) the formula returns 0

If you would prefer that the formula return #N/A if the product isn't in the
list you can use this simpler variation instead:
=VLOOKUP(B2,Products,2,FALSE)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"LB" wrote in message
...
Does anyone know which function(s) I can use to pull certain names from a
list that meet either of two criteria? Below is list of all products
available: I only want to pull products and corresponding data that
either
do not have "sub-product" (i.e. Product A,C,D) or is labled
"sub-product;1"
(i.e. Product B;1, Product E;1 Product F;1).

Product Name Total Sales
Product A 5
Product B;1 10
Product B;2 15
Product B;3 2
Product C 50
Product D 20
Product E;1 25
Product E;2 10
Product E;3 31
Product F;1 34
Product F;2 20
Product G 50
Product H 34

Thanks George. If I only have one column containing the product names, how do
I pull in the name of the product that does not have "sub-product" or is
"sub-product;1" (i.e. Product B;1, Product E;1 Product F;1).

In other words, I only want to pull in the following product names from my
master list below:
Product A
Product B;1
Product C
Product D
Product E;1
Product F;1
Product G
Product H

I'm not sure which functions I should use.


"George Nicholson" wrote:

Assuming
1) your Product table is a 2 column named range called Products
2) Column A contains the value you want to look up in the table.

=IF(ISNA(VLOOKUP(B2,Products,2,FALSE)),0,VLOOKUP(B 2,Products,2,FALSE))
If B2 = Product A, the formula returns 5
If B2 = Product Z (not in the list) the formula returns 0

If you would prefer that the formula return #N/A if the product isn't in the
list you can use this simpler variation instead:
=VLOOKUP(B2,Products,2,FALSE)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"LB" wrote in message
...
Does anyone know which function(s) I can use to pull certain names from a
list that meet either of two criteria? Below is list of all products
available: I only want to pull products and corresponding data that either
do not have "sub-product" (i.e. Product A,C,D) or is labled
"sub-product;1"

(i.e. Product B;1, Product E;1 Product F;1).

Product Name Total Sales
Product A 5
Product B;1 10
Product B;2 15
Product B;3 2
Product C 50
Product D 20
Product E;1 25
Product E;2 10
Product E;3 31
Product F;1 34
Product F;2 20
Product G 50
Product H 34

I'm no longer sure what you are trying to do but maybe
=IF(ISNA(VLOOKUP(B2,Products,1,FALSE)),0,VLOOKUP(B 2,Products,1,FALSE))
Where B2 is the Product name and the 1st column of Products is also the
product name.

--
George Nicholson

Remove 'Junk' from return address.


"LB" wrote in message
...
Thanks George. If I only have one column containing the product names, how
do
I pull in the name of the product that does not have "sub-product" or is
"sub-product;1" (i.e. Product B;1, Product E;1 Product F;1).

In other words, I only want to pull in the following product names from my
master list below:
Product A
Product B;1
Product C
Product D
Product E;1
Product F;1
Product G
Product H

I'm not sure which functions I should use.


"George Nicholson" wrote:

Assuming
1) your Product table is a 2 column named range called Products
2) Column A contains the value you want to look up in the table.

=IF(ISNA(VLOOKUP(B2,Products,2,FALSE)),0,VLOOKUP(B 2,Products,2,FALSE))
If B2 = Product A, the formula returns 5
If B2 = Product Z (not in the list) the formula returns 0

If you would prefer that the formula return #N/A if the product isn't in
the
list you can use this simpler variation instead:
=VLOOKUP(B2,Products,2,FALSE)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"LB" wrote in message
...
Does anyone know which function(s) I can use to pull certain names from
a
list that meet either of two criteria? Below is list of all products
available: I only want to pull products and corresponding data that
either
do not have "sub-product" (i.e. Product A,C,D) or is labled
"sub-product;1"

(i.e. Product B;1, Product E;1 Product F;1).

Product Name Total Sales
Product A 5
Product B;1 10
Product B;2 15
Product B;3 2
Product C 50
Product D 20
Product E;1 25
Product E;2 10
Product E;3 31
Product F;1 34
Product F;2 20
Product G 50
Product H 34