Skip to Main Content

FIU Digital Project Guidelines and Help Materials

The internal standard operating procedures for FIU Libraries' digital collections

About

Occasionally, you may run into instances when you need to “lookup” and match information in a column with information in another column. For example, you may have a list of codes that you need to match to the full text that the codes represent. The following are two methods for looking up and matching information in fields across columns or in different spreadsheets.

VLOOKUP

VLOOKUP requires a lookup table with lookup values in the left-most column. The data you want to retrieve (result values) can ONLY appear in any column to the right. The VLOOKUP formula syntax is =VLOOKUP (value, table_array, col_index, [range_lookup]). 

  • value - The value to look for in the first column of a table.
  • table_array - The table or array from which to retrieve a value.
  • col_index - The column in the table from which to retrieve a value.
  • range_lookup - [optional] 1 or TRUE = approximate match (default). 0 or FALSE = exact match

Here is an example of a VLOOKUP formula accessing information on a separate spreadsheet called "Author". The Author sheet has 865 rows.

VLOOKUP

INDEX and MATCH

Combing the INDEX and MATCH formulas in Excel may look a bit tricky at first. However, this is much more flexible than the VLOOKUP method. 

The INDEX and MATCH syntax are:

INDEX(array, row_num, [column_num])

  • array - this is a range of cells that you want to return a value from.
  • row_num - the row number in array from which you want to return a value. If omitted, the column_num is required.
  • column_num - the column number in array from which you want to return a value. If omitted, row_num is required.

MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value - this is the number or text you are looking for. This can be a value, a cell reference or logical value.
  • lookup_array - a range of cells being searched.
  • match_type -1 or TRUE = approximate match (default). 0 or FALSE = exact match

Here is an example of an INDEX and MATCH formula accessing information on a separate spreadsheet called "Title". The Author sheet has 1801 rows.

Index and Match