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

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

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

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

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

#N/A error.

Using

Using
VLOOKUP

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: