V lookup

Microsoft Excel has a very useful feature known as Vlookup. This post explores how to use Vlookup to compare values of one range of cells with another range.

For example, you may want to get full item name from Inventory Master Sheet for every item id in Physical Inventory Sheet.

Example Sheet

In the XL sheet below, the left side table contains two columns, viz., item id, short name from an XL report, generated through ERP One.

On the right side we have two columns viz, itemid, Item name.

The objective is to write the item name (i.e. full name) in the first table against every row of item id by "looking up" the full item name from the second table.

Formula for Vlookup

Key in cell C4 the following formula for Vlookup():

=VLOOKUP(A4,G:H,2,FALSE)

Here is the explanation for the formula that we are keying in:

A4 - This cell contains Item id which needs to be looked up in another table

G:H - The range of cells which contain the values to be referenced

2 - Column no 2 (i.e. Item name) which needs to be returned from the range of cells (i.e. the table to the right)

FALSE - To specify that XL should exactly match item id from first table, while looking it up in the second table

Once you key in the formula correctly, XL will read the full item name for item id = 000575 from the second table and fill it in cell C4.

Copy/Paste Cell C4 to other cells

Now just copy contents of cell C4 to all other rows below, in the usual way.

Now we have full item name against every item id in the first table, as shown in the illustration.

Instead of copying the master values table (on the right side) in the same XL sheet, Vlookup works from another sheet in the current file, or even from another sheet in a different XL file.