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
- Excel Tips…Using VLOOKUP to check if cell value found in second data set
- Excel Tips…Using VLOOKUP to return custom text for match/no match found
- Excel Tips…Using VLOOKUP and & to CONCATENATE cells before returning custom text for match/no match found
- Excel Tips…Use ‘&’ or CONCATENATE to combine cells
- Excel Tips…Use Conditional Formatting with VLOOKUP to colour cell when match found
Leave a Reply