How to Autofill Values from Another Sheet in Excel

To autofill values from another sheet in Excel, you can use cell references, VLOOKUP or INDEX-MATCH functions. Here are step-by-step instructions for each method:

Using Cell References

1. Open the Excel workbook that contains the two sheets.
2. Click on the sheet where you want to autofill the values.
3. Click on the cell where you want to insert a value from the other sheet.
4. Type an equal sign '=' to begin the formula.
5. Click on the sheet tab that contains the value you want to reference.
6. Click on the cell containing the value you want to reference.
7. Press Enter to complete the formula.

Now the cell in the destination sheet will reference the value from the source sheet. If you want to autofill more values, simply drag the cell with the reference formula to fill the other cells.

Using VLOOKUP

1. In the destination sheet, click on the cell where you want the value to appear.
2. Type the following formula: =VLOOKUP(lookup_value, source_range, col_index_num, [range_lookup])
   • lookup_value: The value you want to look up from the source sheet.
   • source_range: The range of cells in the source sheet where the lookup_value is located.
   • col_index_num: The column number in the source_range from which you want to return the value.
   • [range_lookup]: An optional argument to specify whether you want an exact or approximate match. Use 'FALSE' for an exact match and 'TRUE' for an approximate match.
3. Press Enter to complete the formula.

Using INDEX-MATCH

1. In the destination sheet, click on the cell where you want the value to appear.
2. Type the following formula: =INDEX(source_range, MATCH(lookup_value, lookup_range, [match_type]))
   • source_range: The range of cells in the source sheet from which you want to return the value.
   • lookup_value: The value you want to look up from the source sheet.
   • lookup_range: The range of cells in the source sheet where the lookup_value is located.
   • [match_type]: An optional argument to specify whether you want an exact or approximate match. Use '0' for an exact match, '1' for the nearest value less than or equal to lookup_value, and '-1' for the nearest value greater than or equal to lookup_value.
3. Press Enter to complete the formula.

Example

Here's an example to demonstrate how to autofill values from another sheet in Excel:

1. Assume we have two sheets named Sheet1 and Sheet2.
2. In Sheet1, we have the following data:

   A     B
1 Name  Age
2 John  25
3 Jane  30
4 Jim   20

3. We want to autofill the ages of the names from Sheet1 to Sheet2.
4. In Sheet2, we have the following data:

   A     B
1 Name  Age
2 John  
3 Jane  
4 Jim   

5. To autofill the ages from Sheet1 to Sheet2 using VLOOKUP, enter the following formula in cell B2 of Sheet2: =VLOOKUP(A2, Sheet1!A:B, 2, FALSE)
6. Press Enter and then drag the formula down to fill the other cells. The ages from Sheet1 will be autofilled in Sheet2.

You can also use the other methods mentioned above to achieve the same result.