Formula for extracting data from a table

[olismith89]

Hi experts!

I need a bit of help with a tricky formula I'm trying to get done.

Basically, in cell A2, I'm going to be entering a number and that needs to correspond to a table of prices I have. If the number is between:
0-75,000, it needs to be 478.8
75,001-125,000 it should be 634.8
125,001-150,000 it should be 694.8
150,001-175,000 it should be 718.8
175,001-225,000 it should be 778.8
225,001-280,000 it should be 790.8
280,001-500,000 it should be 958.8
500,001-750,000 it should be 1138.8
750,000-1,000,000 it should be 1378.8
and finally if >1,000,000 then it should be 1978.8.

ALSO, I need a separate formula for a similar table with the following prices: (also for an entry into A2)
0-80,000 should be 40
80,001-100,000 should be 80
100,001-200,000 should be 190
200,001-500,000 should be 270
500,001-1,000,000 should be 540
>1,000,000 should be 910

To clarify, if I enter 250,000 into cell A2, the first formula should come back with 790.8 and the second formula should come back 270.

I hope this helps and that some genius can help with this.

 

[63falcondude]

I put the table of data in columns F, G, and H wherre F is the low end, G is the high end, and H is the value that you want returned.

B2 =IF(AND(A2>=F2,A2<=G2),H2,IF(AND(A2>=F3,A2<=G3),H3... This is for 0-75,000 and 75,001-125,000. Just continue this for all of them.

Second formula is the same idea.

 

[FormR]

If you can set-up your lookup tables like the below example, you could try something like this:

Excel 2013
	A	B	C	D	E	F	G	H	I
1					Table 1	Amount		Table 2	Amount
2	250,000	790.8	270		0	478.8		0	40
3					75,001	634.8		80,001	80
4					125,001	694.8		100,001	190
5					150,001	718.8		200,001	270
6					175,001	778.8		500,001	540
7					225,001	790.8		1,000,001	910
8					280,001	958.8
9					500,001	1138.8
10					750,001	1378.8
11					1,000,001	1978.8
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,$E$2:$E$11,$F$2:$F$11)
C2	=LOOKUP(A2,$H$2:$H$7,$I$2:$I$7)

 

[olismith89]

I put the table of data in columns F, G, and H wherre F is the low end, G is the high end, and H is the value that you want returned.

B2 =IF(AND(A2>=F2,A2<=G2),H2,IF(AND(A2>=F3,A2<=G3),H3... This is for 0-75,000 and 75,001-125,000. Just continue this for all of them.

Second formula is the same idea.

Hi thanks for the help. I didn't want a table messing up my document as I'm a bit of a neat freak, so i substituted the cells for actual numbers, but got it all sorted now. Hopefully an easier one for you as you were so quick to reply last time:

If cell D18 is "Y", I need 6, if D18 is "N" I need 30.

Cheers!

 

[63falcondude]

=if(d18="y","6",if(d18="n","30"))

it keeps changing caps to lowercase but you get the point...

 

[FormR]

I didn't want a table messing up my document as I'm a bit of a neat freak

The tables could go into a hidden sheet; but if you insist - here is the LOOKUP() way without the table..

Excel 2013
	A	B	C
2	250000	790.8	270
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,{0,75001,125001,150001,175001,225001,280001,500001,750001,1000001},{478.8,634.8,694.8,718.8,778.8,790.8,958.8,1138.8,1378.8,1978.8})
C2	=LOOKUP(A2,{0,80001,100001,200001,500001,1000001},{40,80,190,270,540,910})

 

[olismith89]

Sorry, I just noticed that I missed a bit out of the formula and I'm aware I'm being very needy -

If Y, 6 as before, but If N, the 30 needs to be multiplied by a number in column E18

 

[63falcondude]

=if(d18="y","6",if(d18="n",30*d18))