Multiple AND/OR formulas
 

I am building an invoice and I want to have the Price cell populate
dependent on two variables. I figured that out

=IF(AND(B16=B41,C16="Tray"),I41,K41)

Results of this formula if TRUE = 21.00, if FALSE = 1.00

Cells B41 through B47 are products
Cells I41 through I47 are the prices per tray
Cells K41 through K47 are the price for each

But I need to join multiple formulas together to test for each product
and if it is by Tray or not and return a different price for each
product

=IF(AND(B16=B41,C16="Tray"),I41,K41)
OR
=IF(AND(B16=B42,C16="Tray"),I42,K42)
OR
=IF(AND(B16=B43,C16="Tray"),I43,K43)
OR
=IF(AND(B16=B44,C16="Tray"),I44,K44)
OR
=IF(AND(B16=B45,C16="Tray"),I45,K45)
OR
=IF(AND(B16=B46,C16="Tray"),I46,K46)
OR
=IF(AND(B16=B47,C16="Tray"),I47,K47)

Any help is appreciated.

Multiple AND/OR formulas
 

I think you could make good use of the VLOOKUP function in your situation.

See Debra Dalgleish's website for examples and instructions.

http://www.contextures.com/xlFunctions02.html

Does that help?

Post back with more questions.
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I am building an invoice and I want to have the Price cell populate
dependent on two variables. I figured that out

=IF(AND(B16=B41,C16="Tray"),I41,K41)

Results of this formula if TRUE = 21.00, if FALSE = 1.00

Cells B41 through B47 are products
Cells I41 through I47 are the prices per tray
Cells K41 through K47 are the price for each

But I need to join multiple formulas together to test for each product
and if it is by Tray or not and return a different price for each
product

=IF(AND(B16=B41,C16="Tray"),I41,K41)
OR
=IF(AND(B16=B42,C16="Tray"),I42,K42)
OR
=IF(AND(B16=B43,C16="Tray"),I43,K43)
OR
=IF(AND(B16=B44,C16="Tray"),I44,K44)
OR
=IF(AND(B16=B45,C16="Tray"),I45,K45)
OR
=IF(AND(B16=B46,C16="Tray"),I46,K46)
OR
=IF(AND(B16=B47,C16="Tray"),I47,K47)

Any help is appreciated.

Multiple AND/OR formulas
 

Looking up B16 in B41:B47 is best done with VLOOKUP rather than a nesting of
IFs
I do not have time to test but try something on these lines
=IF(ISERROR(VLOOKUP(B16,B41:B47,1,FALSE)),NA(),IF( C16="tray",VLOOKUP(B16,B41:K47,8,FALSE),VLOOKUP(B1 6,B41:K47,10,FALSE)))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
I am building an invoice and I want to have the Price cell populate
dependent on two variables. I figured that out

=IF(AND(B16=B41,C16="Tray"),I41,K41)

Results of this formula if TRUE = 21.00, if FALSE = 1.00

Cells B41 through B47 are products
Cells I41 through I47 are the prices per tray
Cells K41 through K47 are the price for each

But I need to join multiple formulas together to test for each product
and if it is by Tray or not and return a different price for each
product

=IF(AND(B16=B41,C16="Tray"),I41,K41)
OR
=IF(AND(B16=B42,C16="Tray"),I42,K42)
OR
=IF(AND(B16=B43,C16="Tray"),I43,K43)
OR
=IF(AND(B16=B44,C16="Tray"),I44,K44)
OR
=IF(AND(B16=B45,C16="Tray"),I45,K45)
OR
=IF(AND(B16=B46,C16="Tray"),I46,K46)
OR
=IF(AND(B16=B47,C16="Tray"),I47,K47)

Any help is appreciated.