CODEWITHSUNDEEP
excelautofill
KylesDev
KylesDev

Reputation: 99

Autofill column of an Excel table based on another column

I've created a table with multiple columns in Excel, and I'm trying to let it autocomplete some columns based on another one: There's an "Item name" column and a "Item type" one, and I want to make excel autofill the Item Type field on every row based on the entry in the Item name field on the same row, is there a way to make Excel handle this by itself based on previous entries?

EDIT: Example for clarity:
If I insert "Pizza" in Item Name and "Food" in Item type, when I enter "Pizza" again in another row, Excel auto-fills the same row "Item type" field with "Food"

Upvotes: 4

Views: 53141

Answers (1)

Oedhel Setren
Oedhel Setren

Reputation: 159

Based on your edit you want the vlookup function.

VLOOKUP( value, table, index_number, [not_exact_match] )

If index_number is less than 1, the VLOOKUP function will return

VALUE!. If index_number is greater than the number of columns in table, the VLOOKUP function will return #REF!. If you specify FALSE

for the not_exact_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.

But first you'll need to make sure that these associations are already present. You need a table like this. Assume that 'object' is a1 and 'type' is b1:

Object     Type
Pizza      Food
Car        Vehicle

Now for your table that you're entering the values, assume that 'name' is d1 and 'type' is e1:

Name     Type
Pizza    =vlookup($d2, $a$2:$b$99, 2, false)
Car      =vlookup($d3, $a$2:$b$99, 2, false)

The '$' will prevent the relevant cells from changing if you use autofill to complete the column.

If this is a set list that is relatively short you can manually enter it into the autfill options.

Microsoft gives you all the various ways to do this. https://support.office.microsoft.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db?CorrelationId=99c7dec0-4c5c-4988-b148-6d3b7c4dd2e5&ui=en-US&rs=en-US&ad=US

Also, creating a pivot table may be what you want to do. Simply highlight your main table and go to the insert table and click pivot table. Move the headers into the appropriate box.

The last option may be your best bet if it involces a ton of values. lookup/match functions will give you a more robust formula that is more versatile.

If you can give a sample set of data and what you want it to look like I can give you the formula.

Upvotes: 4

Related Questions