Thanks for the feedback, Mike - glad to be of help.

Pete

On Aug 9, 3:42 pm, mike wrote:
Hi Pete many thanks for your help seems so simple now :-)



"Pete_UK" wrote:
You seem to be adding D4 onto the price - you can get the price with
VLOOKUP, so try this formula instead of your multiple IF:

=D4+VLOOKUP(B4,$M$4:$N$100,2,0)

Here I have assumed that you have items up to row 100, but you can
adjust this to suit.

Hope this helps.

Pete

On Aug 9, 3:08 pm, 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- Hide quoted text -

- Show quoted text -