Excel autopopulating columns from a list

November 11, 2015 12:15 PM

What is the formula to have Excel pull information from a list based on a specific identifier?

So this is probably easy but I'm not sure what to Google to find the answer... I'm creating a furniture inventory, and I have all the items typed in a list. I want a formula to auto fill some details in columns B/C/etc. after just typing the item # in column A.

So my list has Item #, Description, Cost. Example: SCU, Mesh Chair, $300

Then I have a separate sheet for each room. I want to just type SCU in column A and have it auto fill from the list the description and cost in columns B and C.

What is the formula for that? Thank you!

posted by OnTheLastCastle to Computers & Internet (7 answers total)

The function is called VLOOKUP iirc
posted by CheesesOfBrazil at 12:15 PM on November 11, 2015

Best answer: More detailed answer, from my D&D sheet:

So in D&D, you have attribute scores (e.g. Dexterity of 13) and each such score correlates with a modifier to certain dice rolls (for 13, that modifier is +1).

So in my D&D excel sheet, the attribute score goes in cell A2, then cell B2 contains this:
=VLOOKUP(A2,AttributeModifiers!$A$1:$B$101,2,FALSE)
...where "AttributeModifiers" is the name of another, hidden sheet in the workbook that lists the attribute scores from 1 to 101 in column A and their associated modifiers in column B. (I don't remember what the ,2 and the ,FALSE mean.)

Thus, when I type "13" into cell A2 in the main sheet, cell B2 auto-fills with "+1".

So you'd want two such formulas if you want columns B and C to auto-fill accordingly.
posted by CheesesOfBrazil at 12:27 PM on November 11, 2015

Best answer: Index Match is a much better tool than Vlookup. Even if it took me a long time to acknowledge it. Vlookup is slower (doesn't matter in small spreadsheets), and also requires the identifier to be in the first column.

The format is as follows:

=INDEX(Column with description, MATCH(Column with item #, cell referencing item #, 0))

0 is an exact match, -1 the nearest match lower or equal, 1 the nearest match higher or equal.
posted by politikitty at 12:42 PM on November 11, 2015 [5 favorites]

Indeed, INDEX + MATCH is very often easier and better than VLOOKUP.
posted by ssg at 12:48 PM on November 11, 2015

Response by poster: Thank you! It's easiest for me to see the formula then puzzle out how it works and both of those are excellent. I appreciate the help!
posted by OnTheLastCastle at 1:43 PM on November 11, 2015

For posterity in Vlookup the parameters are:
=VLOOKUP(Cell,Range,column,boolean)

Cell = cell containing the reference to perform a vertical lookup
Range = range containing the data on which to perform the vlookup the reference column must be the leftmost column
column = is an integer which is what column you want when a match is found in Cheeses example he wanted the second column but he could have multiple column within the range and could select the third fourth etc to get different cells.
boolean = will you accept near matches or not true is yes will accept and false is will not accept near matches, I always leave it on false as otherwise odd behaviour is seen.

for instance if you have:

_A B C
1 1 5 6
2 2 8 9
3 3 2 3

where a cell H6 contains 2
and do =VLOOKUP(H6,A1:C3,3,FALSE) you will get 9.
posted by koolkat at 1:31 AM on November 12, 2015

My INDEX+MATCH usually contains conditional show/hide logic, so that the cell appears empty if there's no match. E.g.:

=IF(OR(ISBLANK(D2),COUNTIF(ListsLookups!A$31:A$281,D2)=0),"",INDEX(ListsLookups!D$31:D$281,MATCH(D2,ListsLookups!A$31:A$281,FALSE),1))
posted by christopherious at 3:44 PM on November 12, 2015