vlookup with Match

Hard-coding the col_index_num in the vlookup formula is not a wise choice as you might have to look into the formula and make changes every time you copy and paste your formula.  An alternative approach of marking column index on top of source table was discussed in the previous post.  However using MATCH function should be a better approach.

MATCH is a good partner to vlookup as it gives you flexibility in assigning the col_index_num.  Before we proceed, let’s have a brief explanation on MATCH.


Similar to vlookup, MATCH returns the position (not value) of a “matched lookup_value” in a lookup_array.  In the example above, $A$2:$E$2 forms the lookup_array {“Rank”,”Movie Title”,”Studio”,”Gross”,”Open”}:

Results for different lookup_value as follow:

  • “Rank” is the 1st item in the lookup_array – returns 1 as a result
  • “Movie Title” is the 2nd – returns 2
  • “Studio” is the 3rd – returns 3
  • “Gross” is the 4th – returns 4
  • “Open” is the 5th – returns 5

Substituting this MATCH function into the col_index_num makes perfect sense, isn’t it?

Your final vlookup formulae in H3 should be


 (pls observe the placements of the $ sign)

  • Lookup_value – $G3, i.e. input variable
  • Table_array – $A$2:$E$12, i.e. source data table
  • Col_index_num – MATCH(H$2,$A$2:$E$2,0), i.e. dynamic number depends on the “header” you want
  • Range_lookup – FALSE, i.e. Exact match is required

Reminders for setting the MATCH function above:

  1. Reference the lookup_value correctly (in our example: H$2, NOT the headers on the source data table)
  2. Make the lookup_value column relative and row absolute
  3. The lookup_value must be identical to the headers used in source data table; “Copy and Paste” is recommended
  4. Make the lookup_array in MATCH both column and row absolute.

(Note: there are three different [match_type] for MATCH function. Pls bear in mind that we use only match_type “0”, i.e. exact match, for this situation)

With the vlookup formula set up as above, you now enjoy the flexibility of retrieving data from different columns by changing the corresponding header instead of revising the formula itself.


Moreover, you may copy and paste your formula without further editing needs.

Other topics about vlookup:

  1. The basic of vlookup
  2. Tips in constructing vlookup
  3. vlookup with Match 
  4. vlookup options – True or False?
  5. Advanced vlookup – Text vs. Number
  6. Advanced vlookup – Wildcard Characters “?” and “*”
  7. Alternative to vlookup – Index and Match
  8. Three different ways to do case-sensitive lookup

Descriptions of different arguments for MATCH (from Excel Help)

The value you use to find the value you want in a table. The argument lookup_value is the value you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.
The argument lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

A contiguous range of cells containing possible lookup values. The argument lookup_array must be an array or an array reference.


  • If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The argument lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
  • If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. The argument lookup_array can be in any order.
  • If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The argument lookup_array must be placed in descending order: TRUE, FALSE, Z-A,…2, 1, 0, -1, -2,…, and so on.
  • If match_type is omitted, it is assumed to be 1.
This entry was posted in Formula and tagged , . Bookmark the permalink.

8 Responses to vlookup with Match

  1. Matthew Kuo says:

    Thanks for the tutorial. VLOOKUP MATCH is very popular and useful to know. Here are a couple of other lookup options that you can consider.



  2. Pingback: 3D VLOOKUP – Perform VLOOKUP from more than one table | wmfexcel

  3. Pingback: Alternative to vlookup – Index and Match | wmfexcel

  4. Pingback: vlookup – Text vs. Number | wmfexcel

  5. Pingback: vlookup – True or False?? | wmfexcel

  6. Pingback: Tips in constructing vlookup | wmfexcel

  7. Pingback: The basic of vlookup | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s