Lookup a value from a table and return a result using a formula

[stefanov07]

HI, I need to somehow lookup "where in a range" is my value and then based on the position, use a formula in the result field. Same as LOOKUP function but instead of returning a value, I need to return a formula that is using the original value that was looked up.

Example in picture: I put "18" in my field. I looks in a range (1-10, 10-100, 100-1000), then since it found that I am in the 10-100 range it returned a value pulled from a predetermined formula for that range. (example from picture: =5*18-10)

Please help. Thank you.

 

[jtakw]

Hi,

Why not just have the formulas in Column F (in my sample) perform the calculation and do a LOOKUP?

Book3.xlsx
	A	B	C	D	E	F
1
2		Value		Range		Formula
3		21		1	9	102
4				10	99	95
5				100	999	90
6
7		Output
8		95
Sheet948

Cell Formulas
Range	Formula
F3	F3	=5*B3-3
F4	F4	=5*B3-10
F5	F5	=5*B3-15
B8	B8	=LOOKUP(B3,D3:D5,F3:F5)

 

[stefanov07]

Hi jtakw, thank you for replying. I guess when I placed the problem statement I oversimplified it.
I actually need the formulas to reference "a general field" instead of that specific field (in your case B3). I need this because I have a table of fields (in my case 10) that all need to look to the table for reference, and then pull a result with a formula that references the value in the selected field. Like in the picture. Just in the picture unfortunately it is not a formula(since I dont know how to do it
, it is just a plain text. I hope it makes sense.

 

[jtakw]

Would something like this work?

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Value	18	25	36	45	40	75	140	500	3
4	Output	80	115	170	215	190	365	685	2485	12
5
6
7				1	9	3
8				10	99	10
9				100	999	15
Sheet948

Cell Formulas
Range	Formula
B4:J4	B4	=5*B3-LOOKUP(B3,$D7:$D9,$F7:$F9)

 

[stefanov07]

Would something like this work?

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Value	18	25	36	45	40	75	140	500	3
4	Output	80	115	170	215	190	365	685	2485	12
5
6
7				1	9	3
8				10	99	10
9				100	999	15
Sheet948

Cell Formulas
Range	Formula
B4:J4	B4	=5*B3-LOOKUP(B3,$D7:$D9,$F7:$F9)

:D:D Im sorry, again I oversimplified the problem, thinking there will be a generic function that will work. Your solution works for the problem as I present it, but not for my real case. In reality the functions in the different tears are unrelated (they are not just a one number difference, as presented in the example). I actually need the "LOOKUP" function to then turn to a more complex formulas.