Using VLOOKUP or MATCH to Find a Value in a Table in Excel 2007:-


The VLOOKUP function does a vertical lookup. It looks for a particular value in the first column of a lookup table. When the first exact match is found, Excel returns a particular column from that row of the table.

VLOOKUP usually has four arguments: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

1. Lookup value—This is the name or value you are trying to find. In the Figure (given below), it is the name in cell A1.



(Examples of the VLOOKUP and MATCH functions.)

2. Table array—This is a rectangular range of cells. Excel searches the first column of the table array in an effort to find a cell that has the same value as the lookup value. In the above Figure, this is the range C10:E33. If you plan on copying the VLOOKUP formula in either direction to find additional customers or columns, you should use the F4 key to make the table array absolute.

3. Column index number—This specifies the column Excel should return as the result of the formula. The columns are numbered 1, 2, 3, and so on, starting with the column that contains the lookup value. In the above Figure, specifying 2 would give you the Q1 quota for the person. Specifying 3 would give you the Q1 sales. Note that specifying 1 would give you the person’s name again. While this might seem silly, since you already have the person’s name as the lookup value, you can sometimes use VLOOKUP to test to see if the lookup value is a valid name in the table array. In this case, it is fine to specify 1 as the column index. Note that if you need to copy this function across several columns in order to return the second, third, fourth, and fifth columns from table array, you can use COLUMN(B1) instead of using 2 as the third argument. As the formula is copied to the right, Excel automatically returns COLUMN(C1), COLUMN(D1), COLUMN(E1), and so on, which then ask for the third, fourth, and fifth columns from the table array.

4. Range lookup—This is either TRUE or FALSE. You specify FALSE to indicate that you are looking for an exact match. In the FALSE version, the table array can be in any order. If you instead specify TRUE, Excel returns the value of the closest match that is equal to or lower than the lookup value. In the TRUE version, the table array must be sorted in
ascending order. If you leave off this parameter, it is the same as specifying TRUE.

VLOOKUP is a workhorse function in Excel. If you’ve ever dabbled in Access, you might have joined tables in order to bring new columns from a lookup table into a query. The VLOOKUP function allows you to simulate joining tables in Excel.

In the above Figure, a VLOOKUP function in cell D1 asks for the second column of the table array and finds the Q1 quota for the sales rep listed in cell A1. A second VLOOKUP in cell D2 asks for the third column of the table array and finds the Q1 sales. The HLOOKUP can do a horizontal lookup, in case your table array has the key values across one row.

There is a curious variant of VLOOKUP called the MATCH function, which looks for a lookup value in a one-column-wide lookup array. When a corresponding value is found in the lookup array, Excel tells you the row number within the lookup array where the match is found. Match can also find a value in a one-row-tall lookup array, similar to the HLOOKUP function.

The MATCH function requires arguments that are similar to the first, third, and fourth arguments of the VLOOKUP function:

1. Lookup value—This is the value that you are trying to find.

2. Lookup array—This is the first column of the table array range from VLOOKUP. This is the column where Excel looks to find a match for the lookup value.

3. Match type—This is conceptually similar to using TRUE or FALSE as the fourth argument in the VLOOKUP function. A FALSE in VLOOKUP requires an exact match. In the MATCH function, a 0 requires an exact match. A TRUE in VLOOKUP returns the next-smallest value from a sorted table array. Similarly, a 1 in MATCH returns the next-smallest value. (Remember that spreadsheets store TRUE as a 1.) MATCH offers one more option for match type. If you specify -1, Excel finds the next-largest value from the descending sorted lookup array.

In the above Figure, a MATCH function in cell D3 looks for the sales rep from cell A1 in the range C10:C33. Note that although the match is actually on row 21, this row is the 12th row in the table array. Thus, the MATCH function returns 12.

Both the VLOOKUP and MATCH functions are CPU intensive, particularly when the lookup table contains thousands of records. The lengthy part of the function is finding the matching record from the first column of the lookup table. After Excel locates that value, moving right to grab the second or third column is relatively quick.