Excel Drop Down with IF

问题:

I want to create a sheet that uses drop downs and fills in some blank spaces based on selection.

Let's say the drop down has Apple, Banana, Cucumber, and Pear. When I create the tables for each one, I have additional information for each one. A1 is the finding word (vlookup) and the other 3 in this case are additional pieces of information.

Sheet(Data)

A1: Apple
A2: Made in USA
A3: Red
A4: Round

On Sheet1, I have a drop down. When I choose Apple on A20, I'd like to fill in A21, A22, A23 with the descriptions as above. So when choosing Apple, it fills it in. If I chose Banana, it could have different variables with it depending on what I put under Banana.

I'm familiar with IF statement, but it seems like it would be too many variables. I have 15 "fruits" to choose from and each one may contain something different to fill in those spaces.

Would this be a possibility?

回答1:

There are many ways to do what you're asking. Vlookup is probably the simplest.

First, you need a table somewhere which has all of your data in it. Something that has all of your fruits in one column, and then a column for each attribute (location, colour, etc.) to the right.

So on sheet2, you could put your raw table data. Starting at A1, you type your fruits on column A, etc.

Then your formula on sheet 1 would look at what you've typed in cell A1, and pull data from sheet 2, based on that value. For example, sheet1 A2, which looks for the fruit's location, would be:

=vlookup(A1,sheet2!A1:D5,2)

Assuming your table on sheet2 starts at A1 with fruits on column A, and location in column B, and it goes only to row 5.

回答2:

You can also use the INDIRECT function to help correlate between two lists.