Excel Autofill Based on Cell Input: Formulas That Work

To autofill related information when someone enters a value in Excel, put a lookup formula in the result cell and fill it down, or use an Excel table so the formula copies to new rows automatically. For example, entering a product code can return its description and price, while a separate formula calculates the line total. The formulas update when the input changes; they do not replace the input cell.

What You’ll Need

Excel for Microsoft 365 on Windows. XLOOKUP, used in the main example, is also available in Excel 2021 and later.
A list of input values and the information associated with each one, such as product codes, descriptions, and prices.
A blank worksheet area for the entry form and the lookup list. Keep the lookup list organized in columns with one record per row.

Step-by-Step

Plan the layout. In this example, the working area has a product code in A, quantity in B, description in C, unit price in D, and line total in E. The lookup list is in G:I: product codes in G2:G5, descriptions in H2:H5, and prices in I2:I5. Put headings in row 1, such as Code, Quantity, Description, Unit price, and Line total. In the lookup list, use headings such as Code, Description, and Price. Keep the codes consistent in both places; for example, do not store a code as a number in one place and as text in the other.
Enter a small lookup list. For a concrete example, use codes P101, P102, P103, and P104 in G2:G5. Put Notebook, Pen, Folder, and USB drive in H2:H5, respectively. Put 4.50, 1.25, 2.75, and 12.50 in I2:I5. Each code must appear once in the lookup list, because XLOOKUP returns the first matching row if there are duplicates. You can replace this sample list with your own data.
Enter a code and quantity. Type P104 in A2 and 3 in B2. These are example inputs; the description and price cells will be calculated from the lookup list. Leave the formula cells C2:E2 available for formulas rather than typing results into them. If your real input cells are elsewhere, use those addresses in the formulas instead.
Return the matching description. With product codes in G2:G5 and descriptions in H2:H5, and the input code in A2, enter this in C2: =IF(A2="","",XLOOKUP(A2,$G$2:$G$5,$H$2:$H$5,"Not found")). When A2 contains P104, C2 displays USB drive. The IF check leaves C2 blank until a code is entered; the final XLOOKUP argument displays Not found if the entered code is absent. The dollar signs lock the lookup ranges when you copy the formula down.
Return the matching unit price. With product codes in G2:G5, prices in I2:I5, and the input code in A2, enter this in D2: =IF(A2="","",XLOOKUP(A2,$G$2:$G$5,$I$2:$I$5,"Not found")). For P104, D2 displays 12.5, which is the price stored beside P104 in the sample list. Excel may display it as $12.50 after you apply a currency format. To format it, select D2 or the intended price range and choose Home > Number format, then select Currency or Accounting.
Calculate a result from the input and lookup. With quantity in B2, unit price in D2, and the product code in A2, enter this in E2: =IF(A2="","",B2*D2). For P104 and a quantity of 3, the result is 37.5 because 3 multiplied by 12.5 equals 37.5. The formula keeps the total blank when A2 is blank. If you also need to prevent a total when quantity is missing, enter a quantity before relying on the result, or adapt the blank check to include B2.
Fill the formulas into more rows. Select C2:E2 and point to the small square at the bottom-right corner of the selection, called the fill handle. Drag it down through the rows you plan to use. Excel adjusts the input references for each row (A2 becomes A3, for example) while the dollar-locked lookup ranges stay fixed. You can also select the formula cells and the cells below them, then use Home > Fill > Down. On Windows, Ctrl+D fills down; on Mac, use Control+D in Excel. If the shortcut behaves differently with your keyboard or system settings, use the ribbon command.

For a list that will grow, you can instead turn the entry range into an Excel table. Select the range, including its headings, and choose Insert > Table; confirm that My table has headers is selected. When you enter a formula into a table column, Excel normally copies it through that calculated column and applies it to new table rows. This is useful when new records are added regularly, because you do not have to drag the fill handle each time. Keep the lookup list separate from the entry table so adding rows to one does not disturb the other.

The formula cells recalculate when their input or lookup data changes, provided calculation is set to Automatic. Check Formulas > Calculation Options > Automatic if a result appears stale. The exact ribbon labels can vary slightly by Excel release, but Calculation Options is on the Formulas tab in desktop Excel for Windows.

Common Mistakes

Typing into a formula cell. If you type a value directly into C2, D2, or E2 after adding a formula, that action replaces the formula. Put user-entered values only in the designated input cells, and restore the formula if a result cell has been overwritten.
Using inconsistent codes. Extra spaces, different punctuation, or a text-versus-number mismatch can stop a lookup from finding the intended record. Make sure the input and lookup codes use the same spelling and data type. Codes with leading zeros should generally be stored consistently as text so Excel does not remove those zeros.
Leaving the lookup range unlocked. If you copy a formula down and its lookup range shifts, it can start searching the wrong cells. Use dollar signs around fixed ranges, such as $G$2:$G$5, and check a copied formula in the next row.
Putting duplicates in the lookup list. XLOOKUP returns the first match it finds. Duplicate codes can therefore return a valid-looking but incorrect description or price. Remove duplicates or decide on a unique key, such as a product code plus size, before relying on the result.
Assuming AutoFill creates a conditional rule. Dragging the fill handle copies a formula or pattern; it does not make a value appear by itself in a cell with no formula. To make results respond to input, put formulas in the result cells or use a table calculated column.

Tips

Use clear input and output columns. Label the cells people should edit and consider applying a light fill color to them. Keep formula cells visually distinct, and explain what users should enter in a note or instruction near the headings.
Make the lookup list a table too. A table with named columns can expand as you add records, reducing the chance that a fixed range misses newly added codes. If you change the formula to use table column references, enter the formula in the entry table and verify that Excel accepts the references and fills the calculated column.
Use a dropdown for controlled inputs. Select the code-entry cells and choose Data > Data Validation. Set Allow to List and set the source to the code list, for example =$G$2:$G$5. This helps prevent spelling differences. A fixed source range will not include codes added below row 5 unless you extend the source or base it on an expanding table.
Format results for readability. Apply currency formatting to prices and totals, and use a suitable number format for quantities. Formatting changes how a result appears, not the value used in calculations. In the sample, the underlying total remains 37.5 even if it displays as $37.50.
Check the behavior with several inputs. Test a known code, another code, and a code that is not in the list. Also test a blank input. This confirms that the lookup, no-match message, and blank behavior are appropriate before others use the sheet.

Troubleshooting

The result says Not found. Compare the entry with the lookup list character by character. Check for leading or trailing spaces and confirm the same code is stored as text or as a number in both places. Also confirm the lookup ranges include the relevant row. If the list has grown past row 5, the sample formula will not search the added rows until its range is expanded or replaced with an expanding table-based reference.

The formula appears as text instead of calculating. Select the cell and check Home > Number Format; change Text to General, then edit the formula and press Enter. Also check that the formula starts with an equals sign and that there is no apostrophe before it. If formulas are displayed throughout the worksheet instead of results, toggle Show Formulas from the Formulas tab or press Ctrl+` on Windows. On many Mac keyboards, the equivalent key combination uses Control+`; keyboard layouts can differ.

The formula returns the wrong row or price. Check that the return range matches the lookup range row for row: G2:G5 must be paired with H2:H5 for descriptions or I2:I5 for prices. Confirm that the code column contains unique values. If you copied a formula, inspect its ranges for missing dollar signs and make sure the input reference moved to the correct row.

New rows stay blank. If you filled formulas only through row 20, a new input on row 21 has no formula to calculate its result. Extend the fill range, or use an Excel table and add the new record inside the table. If a table does not copy formulas automatically, verify that the formula is in the table column and that the new row is part of the table rather than directly below it.

Nothing updates after changing a value. Confirm that the result cell still contains a formula, then check Formulas > Calculation Options > Automatic. In Manual calculation mode, press F9 to recalculate the workbook. If only one row is affected, compare its formulas with a working row; it may have been overwritten or may refer to a different input cell.

Frequently Asked Questions

Can Excel fill a cell automatically when I type in another cell?
Yes, if the destination cell already contains a formula that refers to the input. For instance, the lookup formulas above show a description or price after a code is entered. Excel does not generally write a permanent value into a neighboring cell just because you typed something; a formula or another automation method must provide the behavior.

Can I autofill based on a cell’s text without a lookup table?
Yes. A formula can test for a specific value and return a chosen result, but a lookup list is easier to maintain when there are many possible inputs. For a small number of conditions, use an IF-based formula; for a growing set of codes and results, keep the mappings in a separate list and use a lookup.

Does XLOOKUP work in every version of Excel?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016. In older versions, use a compatible lookup approach such as INDEX and MATCH, or open the workbook in a supported version and replace the formula with one that your edition recognizes.

Can I autofill the formula without dragging?
Yes. Select a formula cell and the cells beneath it, then choose Home > Fill > Down or press Ctrl+D on Windows. You can also double-click the fill handle when there is a continuous neighboring column of data; Excel uses that adjacent data to estimate how far to copy. Check the final row because gaps in neighboring data can affect where the fill stops.

Will the formula update if I change the lookup list?
Yes. If the matching description or price changes within the formula’s lookup range, the returned result updates after recalculation. If you add a new record outside a fixed range such as G2:G5, expand the range or use a table-based lookup so the new record is included.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.