Drop Down Lists, IF and VLOOKUP combined HELP!

[Newbs480]

Hello, so I have an issue that I think requires a combination of the following: drop down list, OR function, IF function and VLOOKUP.


In simple terms we want users to be able to select options from 4 different drop down menus and then give a result based on their choices.


Initially we thought it would be a case of combining IF and VLOOKUP, however there are multiple options in each of the drop down lists.


For the sake of clarity if you can picture that I need cell F3 to show the final result and I have drop down lists in A3 (Apple, Pear, Banana), B3 (Chocolate, Vanilla, Caramel), C3 (Yes, No), D3 (Yes, No).


The result in F3 which will come via a VLOOKUP to a separate sheet, and will vary dependent on the selections for each of A3, B3, C3 and D3.


Hopefully this is clear enough for someone to help me devise a formula that would enable this - I am obviously au fait with how to do the drop down lists etc, it is purely the formula side of things that I would really appreciate some guidance!


Many thanks in advance to whoever can help!

[JohnTopley]

It would help if you posted a small file showing expected outcome as the result appears to a "multi-criteria" lookup.

[Newbs480]

Excel problem.JPG

The column with sprinkles, brownie etc will change dependent on the fruit selected, additionally the options beside each subsequent option in this column will change dependent on the input to the remaining 3 drop down lists.

[Newbs480]

As an added benefit, can we have it where the user selects all options and then presses a "GO" button and the table of answers then appears in the desired location.

[sourabhg98]

Instead of a picture, attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Newbs480]

Instead of a picture, attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

The image shown is as far as we have got at this stage, because the way we lay out our data in the excel spreadsheet will depend on the answer - chicken and egg situation.

[sourabhg98]

But we are not able to understand your requirements precisely.

It seems as if you need something like "Dependent Drop down".
You can refer here - http://www.excel-easy.com/examples/d...own-lists.html

[Newbs480]

Hopefully the mock worksheet is attached.

Please ask if you have any questions.

One thing to note is that if vanilla is selected in cell B3 then C3 and D3 should automatically be N/A (I don't know if this can also be addressed?)

Many thanks!

[sourabhg98]

Okay, this seems to be the dependent drop down query as I guessed.
One thing I would like to clarify is that why you have different time slots for same types. Like for Apple>>Chocolate>>Orange Sprinke>> you have two slots, before "12 No and after 3 no", and then "12 No and after 3 no".
Can you elaborate upon this?
Moreover don't you need to have other drop downs like for sprinkle, etc..

[Newbs480]

Sprinkles, Buttons, Sparkle and Cream are the variables for "Apple" selection, then responses to the drop downs in Sheet 1 (B3, C3 and D3) will dictate the results that are shown in Sheet 1. The results are the relevant column from Sheet 2 (e.g. Sprinkles=Orange, Buttons=White, Sparkle=No, Cream=Yes)

The columns in Sheet 2 cater for all of the potential answers that a user might give.

Hopefully this clears things up?

[Newbs480]

I would really appreciate any help that someone could give me. If someone can spend a few moments looking at my problem that would be incredibly helpful

[sourabhg98]

Hi,
You need to re-arrange your data in table format in "Data" sheet.
You in the sheet attached you can see there are 4 drop-downs.
So, you need to begin from the first one. You can't select the next drop down until the previous one is selected.
Also, when all 4 drop downs are selected by the user, the next 4 variables are displayed accord to the selections made.

Hope that helps!!