I am creating a form which will act as an advertising purchase order. I want
the price to auto populate and am using If arguments to associate product
with price, i.e....=IF(A20="EPL",25,IF(A20="DB",59,0)). The problem I am
having is that each product can have five different prices according to the
area that the advertising covers. The areas are divided into zones,
megazones, super megazones, state and national buys. There are eight
different advertising products but literally hundreds of zones. If I can set
up a series of "IF" arguments that are dependent on two criteria, product and
zone, I'll have it licked I think. Any ideas?....cause I'm lost!

Thanks

On Sat, 3 Dec 2005 23:21:01 -0800, Payton
wrote:

I am creating a form which will act as an advertising purchase order. I want
the price to auto populate and am using If arguments to associate product
with price, i.e....=IF(A20="EPL",25,IF(A20="DB",59,0)). The problem I am
having is that each product can have five different prices according to the
area that the advertising covers. The areas are divided into zones,
megazones, super megazones, state and national buys. There are eight
different advertising products but literally hundreds of zones. If I can set
up a series of "IF" arguments that are dependent on two criteria, product and
zone, I'll have it licked I think. Any ideas?....cause I'm lost!

Thanks

If I understand you correctly, you could use two data tables and a nested
VLOOKUP function.

1. Set up a price list table with the 8 products listed in Column 1, Rows 2-9;
and the 5 prices listed in columns 2-6. (Row 1 contains the price labels;
Column 1 contains the product labels)

2. Set up a second table with the zone names (or numbers) in column 1; and the
corresponding price lists in column 2.

3. Try this formula:

=VLOOKUP(Product,PriceTable,1+VLOOKUP(Zone,ZoneLis t,2,0),0)

The 2nd VLOOKUP function is acting as the "column" argument for the first
VLOOKUP function.

Various errors will occur depending on:

1. Zone not present in ZoneList table -- returns the name of the product
2. Invalid Price label in Zonelist table -- #REF!
3. Product not present in PriceTable -- #N/A

These errors can be handled in a variety of ways, depending on your
specifications. But the above should be enough to get you started.
--ron

Thanks Ron. I think that will give me a good start. I would like to hear any
other suggestions out there. Other options would give me firmer ground to
stand on next time I'm in over my head.

Payton

"Ron Rosenfeld" wrote:

On Sat, 3 Dec 2005 23:21:01 -0800, Payton
wrote:

I am creating a form which will act as an advertising purchase order. I want
the price to auto populate and am using If arguments to associate product
with price, i.e....=IF(A20="EPL",25,IF(A20="DB",59,0)). The problem I am
having is that each product can have five different prices according to the
area that the advertising covers. The areas are divided into zones,
megazones, super megazones, state and national buys. There are eight
different advertising products but literally hundreds of zones. If I can set
up a series of "IF" arguments that are dependent on two criteria, product and
zone, I'll have it licked I think. Any ideas?....cause I'm lost!

Thanks

If I understand you correctly, you could use two data tables and a nested
VLOOKUP function.

1. Set up a price list table with the 8 products listed in Column 1, Rows 2-9;
and the 5 prices listed in columns 2-6. (Row 1 contains the price labels;
Column 1 contains the product labels)

2. Set up a second table with the zone names (or numbers) in column 1; and the
corresponding price lists in column 2.

3. Try this formula:

=VLOOKUP(Product,PriceTable,1+VLOOKUP(Zone,ZoneLis t,2,0),0)

The 2nd VLOOKUP function is acting as the "column" argument for the first
VLOOKUP function.

Various errors will occur depending on:

1. Zone not present in ZoneList table -- returns the name of the product
2. Invalid Price label in Zonelist table -- #REF!
3. Product not present in PriceTable -- #N/A

These errors can be handled in a variety of ways, depending on your
specifications. But the above should be enough to get you started.
--ron