Hi,
I built a table (PriceTable) that keeps pricing based on volume ordered as follows:
ColumnA ColB ColC ColD
1 ProductName Sign Qty Price
2 ProductA > 10 $3
3 ProductA < 10 $4
When looking at my sales, I would like to use IF and VLOOKUP to check if the sale is more than or less than 10. I know I can just use > or < but my pricing table has close to 60 items so I am trying to use a dynamic formula to check the whole table. For example,
Sale A is for 15 of Product A. They will pay $3 per item
ColumnA ColB ColC
1 ProductName Qty Price
2 ProductA 15 Formula???????
My formula in cell C2 above is
=IF(AND(A2=VLOOKUP(A2,PriceTable,1,FALSE),B2&VLOOKUP(A2,PriceTable,2,FALSE)&VLOOKUP(A2,PriceTable,3,FALSE)),
VLOOKUP(A2,PriceTable,4,FALSE),"")
The formula result is #VALUE ! because it recognizes 15>10 as text instead of values. Can I use something other than & in this formula or is there any other way of doing it?
Thanks in advanced.
I built a table (PriceTable) that keeps pricing based on volume ordered as follows:
ColumnA ColB ColC ColD
1 ProductName Sign Qty Price
2 ProductA > 10 $3
3 ProductA < 10 $4
When looking at my sales, I would like to use IF and VLOOKUP to check if the sale is more than or less than 10. I know I can just use > or < but my pricing table has close to 60 items so I am trying to use a dynamic formula to check the whole table. For example,
Sale A is for 15 of Product A. They will pay $3 per item
ColumnA ColB ColC
1 ProductName Qty Price
2 ProductA 15 Formula???????
My formula in cell C2 above is
=IF(AND(A2=VLOOKUP(A2,PriceTable,1,FALSE),B2&VLOOKUP(A2,PriceTable,2,FALSE)&VLOOKUP(A2,PriceTable,3,FALSE)),
VLOOKUP(A2,PriceTable,4,FALSE),"")
The formula result is #VALUE ! because it recognizes 15>10 as text instead of values. Can I use something other than & in this formula or is there any other way of doing it?
Thanks in advanced.