I'm trying to create an Excel spreadsheet that auto-populates a cell based on data that I put into one cell. For instance, column E is a number that corresponds to an item. The description for the item the number represents is stored in column C on the same row as the number it corresponds to.
I'd like to be able to put in the number in Column E, and have the description in Column C be automatically filled. I already have a sheet in the same Excel workbook that has all the numbers on Column A and all of the descriptions next to it on Column B. How can I get the correct description to be pulled onto the other sheet from the number on Column A?
31 Answer
In column C, use the VLOOKUP function. It takes 3 arguments: The cell with the value you want to look up, the range of cells containing the data to be searched, and the column that the data is in.
=VLOOKUP(lookup_cell, range_start:range_end, value_column)For your example, you have an item number in column E (lookup cell), your data is in columns A and B of another sheet (range), and the item descriptions are in column B (2) of the range. So, if your lookup cell was E1, and you had 2 items in your list on the second sheet, your formula for cell C1 could be this:
=VLOOKUP(E1, Sheet2!A$1:B$99, 2)(Replace the 99 with the number of rows you have on Sheet2.)
If column A had item number 1, and column B had the description "Item 1", then cell C1 would display "Item 1".
(The $ in the range fixes the rows of the range with those numbers, so if you were to 'Fill Down' with column C, the column E in each row would feed the formula for that row's column C and be able to find the correct entry in the lookup range.)
