Lookup in Table then....

[kaustin]

I have a large spreadsheet with lots of daily sales volumes and need to look up the sales number in a table to determine the cost varaible then multiply the total sales by cost. Can anyone help out. I was only able to come up with a simple IF statement to the first range. I need it to look through the min/max and if it is between those variables multiply it by cost.
My formula to calculate the $133,000.00 is:

IF(AND(B10>A2,B10<B2),(B10*C2))

Thanks for helping


Min Max Cost
0 500,000 $0.38
500,001 1,000,000 $0.30
1,000,001 2,000,000 $0.29
2,000,001 3,000,000 $0.28
3,000,001 4,000,000 $0.27
4,000,001 5,000,000 $0.26
5,000,001 100,000,000 $0.25

Total Sales 350,000 $133,000.00
1,00,002
2,000,004
3,000,003
4,000,005
8,000,000

 

[kaustin]

For some reason I get a #N/A.

 

[GorD]

Try this

Book1
	A	B	C	D
1	Sales
2	350000	133,000.00
3	500000	190,000.00
4	500001	150,000.30
5		-
6		-
7		-
8		-
Sheet2

 

[GorD]

Sorry, that last version didn't post properly

Book1
	A	B	C	D	E	F
1	Sales				Lookup Table
2	350000	133,000.00			0	0.38
3	500000	190,000.00			500001	0.3
4	500001	150,000.30			1000001	0.29
5		-			2000001	0.28
6		-			3000001	0.27
7		-			4000001	0.26
8		-			5000001	0.25
Sheet2

 

[kaustin]

I have set up an example just like you have it with the exact same formula and continue to get #N/A. Any idea why, would you think there is something I might have turned off?

 

[GorD]

If you get an N/A, it means that the vlaue you are looking up isn't found in the look up table.

Could it be formatted as text?

 

[kaustin]

I deleted my sheet and it works fine now. Thanks for helping.