← Functions
indexmatchvlookuphlookupdata referencingfunctions

Data Referencing with MATCH, INDEX, VLOOKUP and HLOOKUP

How to use MATCH and INDEX functions for two-dimensional data referencing in Excel, and how they improve on VLOOKUP and HLOOKUP.

There are several functions that can do data referencing in Excel: LOOKUP, VLOOKUP, HLOOKUP, MATCH, INDEX, and OFFSET. This article primarily focuses on the MATCH and INDEX functions, and also covers how the MATCH function can improve the uses of VLOOKUP and HLOOKUP.

MATCH Function

The MATCH function returns the relative position of an item in a range. Syntax:

MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value — the value you want to find
  • lookup_array — the range of cells being searched
  • match_type — -1, 0, or 1 (0 = exact match, default is 1)

Example: =MATCH(B19,B4:H4,FALSE) returns 5 if "Brazil" is the fifth item in the country range B4:H4.

Combining MATCH with VLOOKUP

You can combine MATCH with VLOOKUP to enable two-dimensional lookups:

=VLOOKUP(B20,A4:H16,MATCH(B19,B4:H4,FALSE)+1,FALSE)

Here the MATCH result feeds directly into the col_index_num argument of VLOOKUP. The +1 adjusts for the fact that the data range includes the row headers.

Limitations of VLOOKUP and HLOOKUP

Both VLOOKUP and HLOOKUP only look up data in one direction — VLOOKUP to the right, HLOOKUP downward. You cannot use a negative value for col_index_num or row_index_num, which makes them awkward when your lookup column isn't the leftmost column.

INDEX Function

INDEX is more flexible. Syntax:

INDEX(reference, row_num, column_num, area_num)

  • reference — the range of data
  • row_num — which row to return
  • column_num — which column to return
  • area_num — optional, selects a range when reference contains multiple areas

Combining INDEX with MATCH

The most powerful pattern is INDEX + MATCH for two-dimensional lookups:

=INDEX(B5:H16,MATCH(B20,A5:A16,FALSE),MATCH(B19,B4:H4,FALSE))

  • First argument: the data range (B5:H16)
  • Second argument: MATCH locates the correct row (month)
  • Third argument: MATCH locates the correct column (country)

This returns the exact intersection — for example, June sales for Japan.

Adding Conditional Formatting

To make the result easier to spot in the table, add conditional formatting:

  1. On the Home tab, click Conditional Formatting → Highlight Cells Rules → Equal To
  2. Select the result cell (B21) as the value to match
  3. Choose a custom format — yellow fill works well for visibility
  4. Click OK twice to apply