For me, aside from all the common features that Excel has, VLOOKUP is my single most used function.

When you need to carry out a lot of data consolidation for analysis where there are lots of different data sources to collate and compare, VLOOKUP is an essential tool.

VLOOKUP is far easier to use than it first appears, and can not only save lots of time but more importantly will remove any potential for human error when trying to compare data manually.

Typically you’ll use VLOOKUP across data held in separate worksheets or workbooks, but the method is the same when using for data in the same worksheet.

Summary:

Looks for match of source cell in target range left most column and returns value in the same row.

Arguments:

=VLOOKUP(lookup_value,table_array,column_index_number,FALSE)

e.g.

=VLOOKUP(A1,sheetname!A:C,3,FALSE)

Find match for cell A1 in sheetname Column A and return value in same row column C

Counting from column A, column C=3 – count number of columns from where match is and use this value.

FALSE means that only exact matches are returned.

Sheet1

A B < B function >
1 Ford Red =VLOOKUP(A1,Sheet2!A:G,4,FALSE)
2 Lotus Silver =VLOOKUP(A2,Sheet2!A:G,4,FALSE)
3 BMW Blue =VLOOKUP(A3,Sheet2!A:G,4,FALSE)
4 Audi #N/A =VLOOKUP(A4,Sheet2!A:G,4,FALSE)
5 Mini 0 =VLOOKUP(A5,Sheet2!A:G,4,FALSE)

Sheet2

A B C D E F G
1 Ford US Escort Red
2 Lotus UK Elise Silver
3 BMW Germany 330 Blue
4 VW Germany Golf Black
5 Ford US Mondeo Silver
6 Mini Cooper

No match found returns #N/A

If match found, but the referenced cell is empty 0 is returned.

Note: First match is used so ‘Ford’ in Sheet2 row 1 is used – ‘Ford’ in Sheet2 row 5 is ignored

Also see Excel Tips…Using VLOOKUP to return custom text for match/no match found

Also see