what the heck is 10p? Must have meant cents ;) You want a vlookup not all
that if mess. Look into help for that but basically it will be something like
=vlookup("stockcode on customer page", "productcode and price columns like
A2:B1000",2 "the 2 is because you want to look up the code in column one and
return what is in column 2 the price",false "just because")

hope that makes sense, vlookups are hard to get your head around in the
beginning.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"mike" wrote:

Hi all,

I would like to be able to have a master table set up like the example below:

PRODUCT PRICE
M01 0.10
M02 0.10
M03 0.10
M07 0.20
M08 0.20
M09 0.20
M10 0.30
M11 0.30
M22 0.40
M23 0.40

This master table would then be used to update another table of customers
prices which looks like this:

CUSTOMER CODE STOCK CODE DESCRIPTION PRICE
TMANDA M01 MILK 1 PINT WHOLE
1.10

I have used an if statement which works for the Pints using cell references
and look slike this

=IF(B4=$M$4,D4+$N$4,IF(B4=$M$5,D4+$N$5,IF(B4=$M$6, D4+$N$6)))


but as i understand it you can only have 7 if statements and i have more
that 7 products. So i would like to be able to use and if statement to look
up a range of values ie all the pint codes M01, M02, M03 and just increase
the price accordingly by 10p.

Or another option would be to use an if statement that looked up the text
string ie the word "PINT" instead of the code but im not sure how to do this
:-(

Many thanks in advance for any help.

Regards

Mike