Auto fill cell based on Drop Down List not working

Hi there!

I've been trying this for hours but nothing has worked yet. I tried vlookup, index+match+left , if functions, all unsuccessfully.

I have 2 problems and I've attached a file to be easier to understand.

Problem 1: I need to auto fill a cell based on a selection of a drop down list and on a list on another sheet. But I only need some characters of what is displayed on the drop down option.

e.g. I need to fill the green cell (W6) on 'Sheet1' based on the selections of the drop down (blue cell). If I select the first option '123 Chocolate 456 Strawberry' I want it to select only '123' and look for that reference on another list (on Sheet2) and bring the result of the cell next to it.

Meaning: I want it to look for '123' on the list on Sheet2 but to appear '0,239' on the green cell.


Problem 2: I need to fill the red cell with a variation of a section of a selection on the drop down list (blue cell).

e.g. If I select '123 Chocolate 456 Strawberry' on the drop down, I want to to look for only '456' in that text and display it as 0.456 in the red cell. The problem is that not all numbers have 3 characters.



I would appreciated any help as I've spent way too much time on this already.

Thanks!

Hi, I answered a similar question recently HERE

hopefully this is a starting point

Hi ChemistB!

Thank you so much for your reply! Your tips work wonderfully on the example, but not quite on my real spreadsheet :/

Some things I noticed are different:

1) I renamed my W4 cell, which is a merged cell and is the drop down list, to DropDown. Whenever I try the first formula it comes up a #VALUE error, and when I trace it, it highlights the DropDown cell and the list on Sheet2. I noticed there's a letter right next to the 3 numbers. Could this be it? Because I tried it on the example (after I updated it to be more accurate) and it happens the same thing.

2) the formula for the green cell works wonders for 3 numbers, but when I select the option with only 2 numbers it also comes up a #VALUE error. I believe is because there's a letter right next to the 2 numbers.

I've attached a more accurate example of what is going on and would really appreciate your help.

Thanks thanks thanks!