I don't think your VLOOKUP formula "failed miserably" - rather I think it just needs tweaking.

As I wrote in my last post, the reason you're getting a #VALUE error

__may__ be because you've include 0 (zero, which is equivalent to FALSE) as the fourth argument to your VLOOKUP functions, which instructs Excel that you are looking for an

__exact __match of the lookup value in C17. If Excel cannot find an exact match (and I mean an EXACT match), then it will return an error. If you must return an exact match then you must be certain that the lookup value will exist in the lookup range/s, If not, then change the value of the fourth argument to 1 or TRUE (or omit it completely) to instruct that you are looking for an

__approximate__ match of the lookup value, which will return the

__value equal to or next less than the lookup value__ (often used for sales commission, tax rate scales, etc.). Obviously, the column in the lookup tables in which you're trying to find the lookup value must be column A.

Also ensure that the column number returned by B20 is always within the lookup range A:CI (i.e. must be between 1 & 87 inclusive) otherwise you will get an error.

As I also mentioned in my last post, you could simplify/shorten the formula a little by creating Defined Names for each of the three lookup tables (i.e. 'HIGH SALES CURVES'!$A:$CI, 'STANDARD SALES CURVES'!$A:$CI, 'LOW SALES CURVES'!$A:$CI) and using the names in the formula instead of the direct reference to sheet+range. That is:

- Create the following Defined Names (with Workbook scope):
- "High" which RefersTo: 'HIGH SALES CURVES'!$A:$CI
- "Standard" which RefersTo: 'STANDARD SALES CURVES'!$A:$CI
- "Low" which RefersTo: 'LOW SALES CURVES'!$A:$CI

- Replace the range references in the formula with the corresponding Defined Names.

If you do all the above, your formula will look like this:

=IF(B3="High Sales",VLOOKUP($C$17,HIGH,B$20,1)),IF(B3="Standard Sales",VLOOKUP($C$17,STANDARD,B$20,1)), IF(B3="Low Sales",VLOOKUP($C$17,LOW,B$20,1))

You could eliminate the nest IF functions in your formula by employing the INDIRECT function to lookup the appropriate tab based on the user's selection, but given the relative simplicity of just 3 IFs with identical structure, this is not necessarily an improvement!

See if this gets it working.