Combining a drop down list with a VLOOKUP formula and IF formula

[mainey]

Hi, I am trying to create a formula that will intially look up a cell that has a drop down list with 3 answers, depending on the selected dropdown it needs to do a vlookup on the relevant tab to the drop down list.
I have currently tried writing something along the lines of the below without success.

IF(b3="High Sales",VLOOKUP($C$17,'HIGH SALES CURVES'!$A:$CI,B$20,0)),IF(b3="Standard Sales"VLOOKUP($C$17,'STANDARD SALES CURVES'!$A:$CI,B$20,0)), IF(b3="Low Sales",VLOOKUP($C$17,'LOW SALES CURVES'!$A:$CI,B$20,0))

This currently works on a single tab without the dropdown as below but need to have a few different options.

=VLOOKUP($C$17,'STANDARD SALES CURVES'!$A:$CI,B$20,0)

If you could help me with this it would be much appreciated.

Thanks

 

[BigC]

Hi Mainey, and welcome to the Forum

If I understand your situation correctly:

1. B3 has a Data Validation rule applied that presents the user with three options via a drop-down button
2. C17 returns a value depending on B3 (though not necessarily)
3. Depending on the option selected by the user in B3 , you wish to lookup the value in C17 in the corresponding sheet/tab (i.e. if High Sales is selected, then lookup the table in 'HIGH SALES CURVES' sheet, etc.) and return a value

However, I'm not clear as to the nature of your problem.

Consider the following:

1. Where?​

   ..I am trying to create a formula ..

2. What has not worked?​

   ..I have currently tried writing something along the lines of the below without success...

3. I don't understand what you mean by this.​

   This currently works on a single tab without the dropdown as below but need to have a few different options. =VLOOKUP($C$17,'STANDARD SALES CURVES'!$A:$CI,B$20,0)

4. What is the nature (data type) of the lookup value in C17 (i.e. text, number, date, etc.)? By including 0 (zero, which is equivalent to FALSE) as the fourth argument to your vlookup functions you specifying that you are looking for an exact match of the lookup value in C17, so if you are not getting the return value/s you expect, then this may be the problem.

You could also simplify the lookups 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.


HTH

 

[mainey]

Hi BigC,

Sorry I don't write formulas this complicated usually and if I do get stuck I usually come on here

B3 does contain a data validation drop down button with 3 choices, I will start at the beginning though and come back to this.

I initially wrote this as a standard sales forecast so it was just 2 tabs, one for the summary and one for the sales curves which is dependent on how many units we buy and how many we predict to sell each month to sell out. This is where the first VLOOKUP formula came from: =VLOOKUP($C$17,'STANDARD SALES CURVES'!$A:$CI,B$20,0).

This formula works fine for one sales forecast by looking at C17 to determine how many units we have bought, this then looks at the table on the Standard Sales Curves sheet which has how many we predict to sell each month for up to about 80 months or until we run out (I should probably point out this formula is 1 month and their are up about 80 of this adjacent which are dragged over).
So when you enter the amount of units bought in C17 it gives a prediction of how many we need to sell each month accross the row from the table (B$20 basically is just numbers dragged across to give you which column it needs to look at for each cell/month).

I now need to do predictions depedent on the current markets, High Sales, Standard Sales and Low Sales so there are different sales curves to each of these.
So to try and do this I created the data validation drop down box and the extra tabs so that when I choose which one to use it should look at that tab and input the data from the specific tab. I did it this way for ease of use for people who don't understand the spreadsheets so they can just click on what curve to use and how many units.
This is where I tried to use this formula and other variations: =IF(B3="High Sales",VLOOKUP($C$17,'HIGH SALES CURVES'!$A:$CI,B$20,0)),IF(B3="Standard Sales",VLOOKUP($C$17,'STANDARD SALES CURVES'!$A:$CI,B$20,0)), IF(B3="Low Sales",VLOOKUP($C$17,'LOW SALES CURVES'!$A:$CI,B$20,0))

This obviously failed miserably as I'm on here , the VLOOKUP seems to work fine as it does on a single sheet but it seems to be the way I have used the IF formula that I have got wrong as it just returns #VALUE error or a general error and won't work at all.

Thanks for taking the time to look at this and I hope I have made more sense than my previous post.

Mainey

 

[BigC]

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:

1. 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
2. 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.