Multiple IF statements looking up multiple ranges.

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

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.

 

[Guest]

First, see Debra Dalgleish's coverage of the VLOOKUP function at her website:
http://www.contextures.com/xlFunctions02.html

Then see how she uses it in her Order Form example:
http://www.contextures.com/xlOrderForm01.html

Is that something you can work with?
(Post back if you have more questions)
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

Hi Pete many thanks for your help seems so simple now

 

[Guest]

Hi Ron many thanks for this

First, see Debra Dalgleish's coverage of the VLOOKUP function at her website:
http://www.contextures.com/xlFunctions02.html

Then see how she uses it in her Order Form example:
http://www.contextures.com/xlOrderForm01.html

Is that something you can work with?
(Post back if you have more questions)
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

Hi John,

thanks very much you have saved me a major headache

 

[Pete_UK]

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

Pete