Excel: How to auto-fill cells based on a value from another table
This tutorial explains how to automatically fill in information in a blank table in one sheet based on a value entered into that table, with corresponding data pulled from another table in a different sheet.
For example, in Sheet1, Table1 contains information including serial numbers, names, scores, ages, and sex. In Sheet2, Table2 is blank but has the same column headers as Table1. After typing a serial number from Table1 into Table2, the corresponding name, age, sex, and score will be automatically filled in, as shown in the screenshot below.
Note: The methods provided in this tutorial are tested in Excel 2021. There may be some differences in other versions of Excel.
Auto complete cells from another table
To solve this task, you can combine the INDEX and MATCH functions to create a formula. Select the cell where you want to auto-fill the related content based on the value entered in column A, then copy and paste the formula below:
=INDEX(Sheet1!$A$2:$E$10,MATCH($A2,Sheet1!$A$2:$A$10,0),MATCH(B$1,Sheet1!$A$1:$E$1,0))
Edit the formula according to your specific needs.
| In the formula, Sheet1!A$2:$E$10 refers to the range of your data (excluding headers) in Table1, from which values will be auto-filled. A2 is the cell in Table2 where you input a serial number. This value is used to search for the corresponding related data in Table1. Sheet1!$A$2:$A$10,0 refers to the column in Table1 where the serial number is looked up to match the value entered in Table2. B1 is the column header in Table2, which indicates the specific value you want to auto-complete (like name, age, or score). Sheet1!$A$1:$E$1 represents the header row in Table1, which helps identify the correct column when retrieving the data. |
Press the Enter key to get the first related value, then drag the fill handle to the right to fill in all related values based on A2.
Next, drag the fill handle down to the end of Table 2, and the error values (#N/A) will be displayed.
When you type values in column A, the error values will be replaced with the correct related values.
Other Operations (Articles)
How To Turn Off Auto Complete
Here is an option in Excel that can stop the auto complete.
How To Reduce Excel File Size?
Sometimes, it will take minutes to open or save if the Excel file is too large. For solving this problem, here in this tutorial, it tells you how to reduce the Excel file size by removing the contents or formats which are unnecessary or never used.
Excel: How To Create Or Insert A Bookmark
Have you ever imagined creating or inserting a bookmark for quickly jumping to a specific data range while there is a lot of data in a worksheet or a workbook?
How to apply shading to odd or even (alternative) rows/columns in Excel?
While designing a worksheet, many people tend to apply shading to odd or even (alternative) rows or columns in order to make the worksheet more visual. This article will show you two methods to apply shading to odd or even rows/columns in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!