How to automatically display names when entering code in Excel - Combine IF function and Vlookup function

When working with a list of names in Excel, the number of people in a table will certainly be many and what should you do if you want to find that person's information? Manually searching for each name is not feasible. The method of using the Ctr + F key and entering the name of the employee who wants to find information is also chosen by many people. But this case only applies to tables that do not have the same name. So how to quickly find anyone's information in Excel's nameplate?

In this case, we can combine the IF function and the Vlookup function. We will then assign each person in the table a separate code. And when entering any code, that person's information will automatically appear. So managing employees or students on Excel will be much easier.

How to automatically display names when entering code in Excel

We will work with the student table as below. The requirement is to have a student code in Table 2. Entering the code in Table 1 will automatically display the student's name with that code.

How to automatically display names when entering code in Excel - Combine IF function and Vlookup function Picture 1

Step 1:

In the Full Name column in Table 1, we will enter the formula as below and press Enter =IF(B4='''','''',VLOOKUP(B4,$F$4:$G$7,2,0 )) .

In there:

1. B4 is the student code column in Table No. 1.
2. F4 is the student code column in Table No. 2.
3. G7 is the Full Name column in Table 2.
4. The $ character fixes columns or rows in an Excel table.

How to automatically display names when entering code in Excel - Combine IF function and Vlookup function Picture 2

After pressing Enter, we will see the function symbol in the table as shown below.

How to automatically display names when entering code in Excel - Combine IF function and Vlookup function Picture 3

Step 2:

Next, highlight the Code column and Full name column in Table 2 as reference data for the formula. Then, place the mouse pointer in the cell where the formula was entered in Table 1, then drag down to the cell below the correct number of students to copy the Excel formula .

Thus, the formula combining IF with Vlookup has been applied to all cells in the Full Name column in Table 1.

How to automatically display names when entering code in Excel - Combine IF function and Vlookup function Picture 4

Step 3:

Now in Table 1, you enter code M1 in the Code column and press Enter , immediately the student name associated with that code will be displayed in the Full Name column in Table 1.

How to automatically display names when entering code in Excel - Combine IF function and Vlookup function Picture 5

Finally, you just need to enter that code, or drag the results down to the remaining cells to complete the nameplate. Student ID and name will also automatically display.

How to automatically display names when entering code in Excel - Combine IF function and Vlookup function Picture 6

By combining the IF function and Vlookup function like this, displaying information in Excel data tables will be much faster and simpler. We also do not need to search manually, or search again in case of the same name. Student names will automatically be displayed according to the assigned student code.

Wishing you success!

David Pac

Update 28 July 2024