Using Solver & VLOOKUP together

BTBarrett · Apr 19th, 2002, 06:56 AM

Hi there:

When using the solver function, is it possible to get the VLOOKUP function to run as part of the solver routine?, i.e. some cells influenced by the solver contain the Vlookup command and when I try to run solver it can't seem to handle it. Then, after a minute or so it comes back with a message to say solution has been found but none of the cells have changed!

Any thoughts/possible-solutions gratefully accepted!!

Brendan Barrett

croweld89 · Apr 19th, 2002, 08:00 AM

I had a similar problem when I had vlookup formulas pulling stock prices from another sheet and the solver solving for the best weighted option with a given set of stocks.

As I recall, you cannot have the vlookup formula in the "influenced" section. You will need to save the vlookup formulas as values, run the solver, and given that the numbers do not change, replace the vlookup formulas. (Hopefully there are not too many of them)

You may also want to wait for a more informed opinion to be posted.

Throwing in my 2 cents.
-Dave

Mark W. · Apr 19th, 2002, 08:13 AM

Use of the VLOOKUP function in a Solver model is problematic. Take a look at http://www.solver.com/tutorial6.htm. Especially, note the paragraphs on... "Mathematical Relationships" and "Nonsmooth optimization problems". In fact, you may want to review this entire Solver tutorial (See http://www.solver.com/tutorial.htm). These are the folks who created Solver!

[ This Message was edited by: Mark W. on 2002-04-19 07:14 ]

stevebrooks01 · Nov 2nd, 2009, 05:40 AM

Did anyone ever solve this because I am having the same issue.

Nov 2nd, 2009, 05:40 AM	#4
stevebrooks01 Board Regular Join Date: Feb 2008 Location: Southampton, England Posts: 86	Re: Using Solver & VLOOKUP together Did anyone ever solve this because I am having the same issue.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 06:56 AM	#1
BTBarrett New Member Join Date: Apr 2002 Posts: 1	Hi there: When using the solver function, is it possible to get the VLOOKUP function to run as part of the solver routine?, i.e. some cells influenced by the solver contain the Vlookup command and when I try to run solver it can't seem to handle it. Then, after a minute or so it comes back with a message to say solution has been found but none of the cells have changed! Any thoughts/possible-solutions gratefully accepted!! Brendan Barrett

Apr 19th, 2002, 08:00 AM	#2
croweld89 New Member Join Date: Mar 2002 Posts: 34	I had a similar problem when I had vlookup formulas pulling stock prices from another sheet and the solver solving for the best weighted option with a given set of stocks. As I recall, you cannot have the vlookup formula in the "influenced" section. You will need to save the vlookup formulas as values, run the solver, and given that the numbers do not change, replace the vlookup formulas. (Hopefully there are not too many of them) You may also want to wait for a more informed opinion to be posted. Throwing in my 2 cents. -Dave

Apr 19th, 2002, 08:13 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Use of the VLOOKUP function in a Solver model is problematic. Take a look at http://www.solver.com/tutorial6.htm. Especially, note the paragraphs on... "Mathematical Relationships" and "Nonsmooth optimization problems". In fact, you may want to review this entire Solver tutorial (See http://www.solver.com/tutorial.htm). These are the folks who created Solver! [ This Message was edited by: Mark W. on 2002-04-19 07:14 ]