Question

How do I use VLookup in Excel to add a product name in the next cell to a product ID? Product ID has the number (#1) in its cell and Product Name (XJ-422 Relay) is blank. So the 'E' column has the Product ID and column 'F' needs the Product Name added.

Answer 1

To use a vlookup a reference table is needed. The reference table is usually a master data table. In this case it will be a table which will store the details of all the product ID and the product names. For vlookup to be used, the first column in this master data table should be same as the column which is being looked up. So if you are using Product ID in this case to lookup Product Name, the first column in the master data should be Product ID.

See below image. Assume the product master table is present in column J to K. The 'E' column has the Product ID and column 'F' needs the Product Name added.

The format of vlookup is as below

vlookup(lookup value, lookup array, column number from lookup table, match type)

Lookup value is the value to be looked up which is product ID in this case

Lookup array is the master data from where we need to fetch data. In our case this is J3 to K6

Column number is the column number is the lookup table which needs to be fetched. In our case this is 2 as the product name is present in column 2.

Match Type is the type of match. 0 or false means exact match while 1 or True means exact match.

Hence the formula is our case looks like below

This will give the answer as below