drop down text selection in one cell populating another cell with data from a third cell

[Brian_Karius]

This is a bit confusing, at least to me.

Working on a project, for each item, there are 16 relevant descriptive aspects of the item that are then measured on a scale of 0 to 18.

For example, if the item were one kind of pepper, one of the descriptors, 'slow heat', might be low, at four or five, but 'fast heat' might be at 16. 'Sweetness', 'tanginess', etc, comprise examples of the other possible descriptors.

Now, each food item is assigned values in each of the same 16 descriptors, and those are already assigned.

The problem comes in the second part. There is a column that relates to specific recipes, and that has a drop-down menu in which one selects the name of the descriptor one is going for, 'sweetness', 'tanginess', etc. Once that is selected, the column next to it is to be populated with the item's value for that descriptor. So if the item is the pepper we talked about earlier, that column should be automatically populated by that pepper's value in 'tanginess' once 'tanginess' is selected, so one column will say 'tanginess', and the next will give the value.

So, on the left side of the sheet are the 16 descriptors for a food item and their numerical valued, on the right is the section in which recipes are involved.

I tried using if statements, which worked okay, but are a bit problematic, as new recipes are often added in, but directly copying the rows will mess up the if/then formulas by adding to the cells as written in the formulas.

What I tried, simplified a bit, is, For a formula in a column F6:F21:

=IF (E6:E21="tanginess", B6, IF(E6:E21="Sweetness", B7, IF...

E6:E21 would be a column with the drop-down selecting the flavor, B6 would be where the item's "tanginess" rating is, so I'm wanting to be able to make it so things in column B6:B21 can find their way into F6:F21 based on selections in a drop-down menu in E6:E21.

What I have works, kind of, but it has problems, and just doesn't seem to me to be the right way to do it. Does anyone know a better way that will have less issues?

 

[Brian_Karius]

Okay, I tried using LOOKUP to make things simpler, and am now very confused.

So, basically, I have 16 terms, like 'slow heat', 'sweetness', 'tanginess', etc. They are A6:A21

Each of them have a number, from one to eighteen, assigned to them. Those values are B6:B21

In column E6 is a drop-down menu that is populated by the same terms from A6:A21, so 'sweetness', 'fast heat', etc.

The intention is for F6 to automatically be populated by the number in column B that from the same row in column A.

So, I did this:

=LOOKUP(E6,A6:A21,B6:B21)

So, look for the term in E6 in the A column, then use the corresponding B column number.

Unfortunately, this is not giving the right number in all cases.

Any thoughts on what I could be doing wrong?

 

[Weazel]

without seeing the data its hard to say.

Lookup only does approximate match which would require column A to be sorted in ascending order.

you could try something like, vlookup(E6,A6:B21,2,0) if you're not able to sort.

 

[Brian_Karius]

So, using the lookup approach, the numbers I should get, depending on which category of flavor I choose, should be the top row, but is coming out the bottom row:

9,13,11,10,12,12, 9,17,15,18,17,15,16,17,16,15
15,17,17,17,15,17,16,17,16,18,15,15,16,17,17,15

 

[Brian_Karius]

without seeing the data its hard to say.

Lookup only does approximate match which would require column A to be sorted in ascending order.

you could try something like, vlookup(E6,A6:B21,2,0) if you're not able to sort.

Ah, that would explain why all the lower values totally disappear!

Okay, vlookup does it exactly. Thanks!