[Excel Efficiency] Automatically enter the product name by entering the product number! Techniques to prevent entry errors in books in Excel

After entering the product number in the product sales report sheet, let's automatically transfer the product data from the product catalog sheet.

The VLOOKUP function is useful when you want to retrieve data from a table and transcribe it. The VLOOKUP function is used in the format "=VLOOKUP(search value, range, column number, search method)". According to this format, enter a formula using the VLOOKUP function in cell B3 where you want to display the product name in the daily report.

First, enter "=VLOOKUP(" (①) in cell B3 of the [Daily Sales Report] sheet.

Next, click cell A3 (②). Enter the formula all at once without pressing the Enter key. Click cell A3 to add "A3" (③) after the formula you just entered. You can also type "A3" after the formula without clicking the cell.

Cell A3 will be the "search value". The search value here refers to the product number that is used as a reference when searching for product names from product catalogs. On the daily report sheet, the product number is entered in cell A3, so let's say "A3".

After entering "," (comma) (④) immediately after "A3", switch the sheet once. Click the [Product Catalog] sheet (⑤) and drag to select the cell range A3:C14 (⑥). The formula in the formula bar should have added "Product Catalog!A3:C14".

The cell range A3:C14 selected by dragging will be the "range". The range here is the range for searching the product name from the product number.

In case you copy the formula to other cells later, let's make it an absolute reference so that the "range" doesn't change with the formula in any cell. Correct "A3:C14" in the formula displayed in the formula bar to "$A$3:$C$14" (⑦).

Furthermore, enter ",2,FALSE)" (⑧).

The "2" entered here corresponds to the "column number" of the format. For this column number, specify the column number from the left end of the value to be transferred within the "Range" specified in steps 5 and 6. Here, "Product Name" is in the second column from the left end, so it is set to "2".

Also, the last input "FALSE" is "search method". Specify FALSE if you want to search for an exact match value. If TRUE, finds the largest value that does not exceed the search value. In this example, we need to search for an exact match, so we set it to "FALSE". After you have entered the information up to this point, press the [Enter] key (9).

The display returned to the [Daily Sales Report] sheet, and "#N/A" (⑩) was displayed in cell B3.

You may be surprised to see an error value, but this is because the part number has not yet been entered in cell A3. When you actually enter the product number (11) in cell A3 and press the [Enter] key (12), the product name is displayed in cell B3 (13).

Now, when you enter the product number, the product name will be automatically posted.