worksheet function - Microsoft Excel 2007 - Drop down list that grabs multiple columns from another sheet

I've been scouring the internet for this but can't seem to find a solution.

Let's say I have 2 Sheets - "Items" and "Inventory".

Inside "Items", I have 3 columns:

• Item Name


• Item Type


• Item Cost

Inside "Inventory", I have the same column as above. However, under Item Name in the "Inventory" sheet, I have a drop down list that lists all items found under Item Name in "Items".

What I want to happen is that in "Inventory", if I select an item from the drop down list... I want not only the Item Name to be shown... but the Item Type and Item Cost in "Inventory" should be populated as well with whatever data is in "Items" for the corresponding item I have just chosen.

Is this possible?

Answer

• Yes, using VLOOKUP. Explanation taken from here, courtesy of bigmyk2k in reply to similar question.

The work here is not done in the dropdown (which is easy to make), the work is done in each column you want to populate.

For each cell, write a VLOOKUP which finds the desired value in a table, based on the value in the drop-down cell. In the example above, you will either want to name the ranges of parent data (the information on a separate spreadsheet that you are populating from) or adjust as necessary. For this example, lets assume that we have named the parent data: Parent_Data

A1 B1 C1 D1 Full Name ID Addr1 Addr2

For instance, in the example above, if your drop down for FULLNAME is in cell A1, and you are creating the VLOOKUP for Addr Line 2 would be,

=VLOOKUP(A1,Parent_Data,4,True)

This would find the exact match for your full name, go to the third column to the right of that, and return that value.

To keep it neat, you will want to embed the vlookup in an IF statement, so that if there is no result, it doesn't show anything. That would look something like this:

=IF(ISNA(VLOOKUP(A1,Parent_Data,4,True)),"",VLOOKUP(A1,Parent_Data,4,True))

Additional clarifying instructions:

For this explanation, I have to start with the fact that excel works either down and right, or up and left. In this case, we are in the "Down and Right" realm.

In order for the function to work, the value that you are searching with (Fullname in A1) also has to be in the leftmost column of the parent data you are searching. This is because Excel is going to search Down the first column until it finds that value, and then Right across the row until it comes to the column you specify.

Assuming that Fullname is in the A column, Excel will return the value in column D because we have told it to look in the fourth column of the row in which we find the matching value. This is the "4" in the formula. For the cell that you want to return AddressLine1, you would enter the exact same formula, but with a 3, instead of a 4.

"False" directs the VLOOKUP function to return a value for the query only if it finds an exact match. "True" will return results for the first close match. To be totally honest, I haven't figured out what Excel thinks is close in a text string...

• Here is a VLOOKUP video tutorial on YouTube for additional instruction.