Microsoft Office Tutorials and References
In Depth Information
Using VLOOKUP to Match Two Lists
VLOOKUP searches for a value in the leftmost column of a table and then
returns a value in the same row from a column you specify in the table. The
VLOOKUP function takes the following arguments:
lookup_value — This is the value to be found in the first column of
the table. lookup_valuecan be a value, reference, or text string.
table_array — This is the table of information in which data is
looked up. You can use a reference to a range such as E2:F9 or a range
name such as RepTable.
col_index_num — This is the column number in table_arrayfrom which
the matching value must be returned. A col_index_numvalue of 1 re-
turns the value in the first column in table_array; a col_index_num
value of 2 returns the value in the second column in table_array, and
so on. If col_index_numis less than 1, VLOOKUP returns the #VALUE!
error value; if col_index_numis greater than the number of columns in
table_array, VLOOKUP returns the #REF! error value.
range_lookup — This is a logical value that specifies whether
VLOOKUP should find an exact match or an approximate match. If it is
TRUE or omitted, an approximate match is returned. In other words, if
an exact match is not found, the next largest value that is less than
lookup_valueis returned. If it is FALSE, VLOOKUP finds an exact
match. If one is not found, the error value #N/A is returned. If
VLOOKUP cannot find lookup_valueand if range_lookupis TRUE, it
uses the largest value that is less than or equal to lookup_value. If
lookup_valueis smaller than the smallest value in the first column
of table_array, VLOOKUP returns an #N/A error. If VLOOKUP cannot
find lookup_value, and range_lookupis FALSE, VLOOKUP returns an
VLOOKUP to Match Two Lists
to Match Two Lists
If Excel is used throughout your company, you undoubtedly have many lists
in Excel. People use Excel to track everything. How many times are you faced
with a situation in which you have two versions of a list and you need to
match them up?
In Figure 12.24 , the worksheet has two simple lists. Column A shows last
week ’ s version of who was coming to an event. Column C shows this week ’ s
version of who is coming to an event. Follow these steps if you want to find
out quickly if anyone is new: