VLOOKUP MATCH is one of several possible lookup formulas within Microsoft Excel. This tutorial assumes you already have a decent understanding of how to use VLOOKUP . If you do not, please click here for a beginner’s tutorial on VLOOKUP .

VlookMatch8

Objective

VLOOKUP MATCH is an improved variation of your basic VLOOKUP or INDEX MATCH formula. Using VLOOKUP MATCH allows you to perform a matrix lookup – instead of just looking up a vertical value, the MATCH portion of the formula turns your column reference into a dynamic horizontal lookup as well. VLOOKUP MATCH is mainly useful for situations where you intended to perform heavy editing on your data set after you’ve finished writing your formula. This is because VLOOKUP MATCH gives your lookup formula insertion immunity; whenever you insert or delete a column within your lookup array, your formula will still pull the correct number.

The key difference between using VLOOKUP MATCH versus the basic VLOOKUP formula is that, in addition to your vertical lookup value (what you’ll be looking up down the left side of your table) you’ll also have a column lookup value (what you’ll be looking up across the top of your column headings).

The Syntax

VLOOKUP and MATCH are the two formulas that are combined to perform this lookup. We’ll look at each of the formulas separately before putting them together. The primary formula we’ll be using is VLOOKUP :

=VLOOKUP ( lookup value , table_array , col_index_num , [range_lookup] )

To use this formula, you’ll need a lookup value and a table array. (We’ll address the column index number later and since we are not performing a range lookup, we can leave that part of the syntax blank) In the example below, the lookup value is ID number “5” and the table array is the green box surrounding cells B6:F14.

VlookMatch1

Next we have the MATCH formula:

=MATCH ( lookup value , lookup_array , [match _type] )

The match formula returns a position number based on your lookup value’s location within the array you’ve selected. To use this formula you’ll need both a lookup value and a lookup array. (The match type parameter should be left blank – doing so tells Excel that we want an exact match). In the example below, the lookup value we’ll be using is the State of “WA” and the lookup array is the orange box surrounding cells B6:F6.

VlookMatch2

Putting it Together

The key to VLOOKUP MATCH is that we are replacing the “column index number” syntax of VLOOKUP with the MATCH formula. Perform this combination using the following steps:

Step 1: Start by typing your VLOOKUP formula as you normally would, inputting the proper lookup value and table array for your lookup; in this example the lookup value is ID number “5” and the table array is the green box surrounding cells B6:F14.

VlookMatch3

Step 2: When you get to the column index number input, instead of typing in a hard coded number, start typing in the MATCH formula

VlookMatch4

Step 3: For the MATCH formula’s lookup value, select the cell containing name of the column you want to return from; in this example we want to return a State, so we click on it

VlookMatch5

Step 4: For the MATCH formula’s lookup array, select the row headings of your table array; in this example it is the orange box surrounding cells B6:F6.

VlookMatch6

Step 5: Close off both your MATCH formula and your VLOOKUP formula with two parentheses (doing this simply confirms for Excel that we want an exact match for the MATCH formula and that we don’t want to use a range lookup for the VLOOKUP)

VlookMatch7

How it Works

The MATCH formula we created returns the value 4. Therefore, based on how we arranged the syntax, the VLOOKUP MATCH in this state is basically performing the same function as a VLOOKUP with a column index number of 4.

VlookMatch8

However, the key difference is that this column reference is now dynamic. If I insert or delete a column from my lookup table, my return value will stay the same. See below for an example of the difference in return values between VLOOKUP and VLOOKUP MATCH after inserting a column.

VlookMatch9

After the insertion occurs, the VLOOKUP formula’s column reference remains 4 and is now pulling from the City field. Your return value has changed from “WA” to “Seattle.” However, with VLOOKUP MATCH, since you’ve indicated by name which column you want to pull from, the column reference automatically updates and therefore you maintain the “WA” return value.

Disadvantages

While VLOOKUP MATCH is clearly an improvement over the basic VLOOKUP , there are still drawbacks to using this formula. With VLOOKUP MATCH, every lookup must still start from left to right. This can become problematic if you want to append lookup keys to the right of your dataset. Additionally, your return values are limited to the originally table array you’ve selected. For example, if you were to append one or two columns to the right of your data set, you wouldn’t be able to lookup and return values from those columns without adjusting your table array.

If you want to use a matrix lookup formula combination without these specific limitations, consider using INDEX MATCH MATCH.


Courtesy: mbaexcel.com

Comments

  1. Nandan

    HI Raghu,

    I have some data in excel in which there are 4 columns having UID, Category, Action, Label and hits uid is user id field. I want a report in which i can choose a uid from drop down list and all other data which used by the choosed uid is shown in one place. How can i do it need your help?

    1. Raghu R Article Author

      Assuming you have running data where UID’s are repeated along with other information.

      Steps
      1: List unique UID’s (Reference link)
      2: Create dropdown with unique UID’s
      2: Macro to list the required information for selected UID (A macro can be written in many ways, below is one method using ‘For Loop‘)

      Macro
      Option Explicit
      Sub ListDetails()
      Dim BaseSht, RepoSht As Worksheet
      Dim BaseLastRow, RepoStartRow, RepoLastRow, ReportRow, iRow As Long
      Dim UID As String

      Set BaseSht = Sheet2
      Set RepoSht = Sheet1

      'check if user select any UID
      UID = RepoSht.Range("ThisUID").Value
      If UID <> "" Then
      'get Start and End Row numbers to loop through
      BaseLastRow = LastRow(BaseSht)
      RepoLastRow = LastRow(RepoSht)
      RepoStartRow = RepoSht.Range("ReportHeader").Row + 1

      'clean report sheet to populate fresh data
      If RepoLastRow > (RepoStartRow - 1) Then
      RepoSht.Rows(RepoStartRow & ":" & RepoLastRow).Delete Shift:=xlUp
      End If

      ReportRow = RepoStartRow

      'check if base sheet has information
      If BaseLastRow > 1 Then
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual

      'loop through base data to find the UID related information
      For iRow = 2 To BaseLastRow
      'Report is UID match found
      If UID = BaseSht.Cells(iRow, 1).Value Then
      RepoSht.Cells(ReportRow, 2).Value = BaseSht.Cells(iRow, 1).Value
      RepoSht.Cells(ReportRow, 3).Value = BaseSht.Cells(iRow, 2).Value
      RepoSht.Cells(ReportRow, 4).Value = BaseSht.Cells(iRow, 3).Value
      RepoSht.Cells(ReportRow, 5).Value = BaseSht.Cells(iRow, 4).Value
      ReportRow = ReportRow + 1
      End If
      Next iRow

      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True

      MsgBox "Report generated for selected UID", vbInformation, "Done!"

      Else
      MsgBox "No information available in Base sheet", vbCritical, "Blank Base Sheet!"
      End If
      Else
      MsgBox "Select UID to generate report", vbCritical, "UID?"
      End If

      End Sub

      Function LastRow(sh)
      On Error Resume Next
      LastRow = sh.Cells.Find(What:="*", _
      After:=sh.Range("A1"), _
      Lookat:=xlPart, _
      LookIn:=xlFormulas, _
      SearchOrder:=xlByRows, _
      SearchDirection:=xlPrevious, _
      MatchCase:=False).Row
      On Error GoTo 0
      End Function

      Working file is here

      Check and let me know of any questions.

  2. Alphonse

    A trick to get through the disadvantage, appending lookup keys to the right of the dataset, is to insert the column(s) before the last column and then move the last column inward. By doing this the new columns, now at the right end of the dataset, will be included with no other steps needed to include them. None of the formulae, if any, pointing to the last column before the insertion are affected by this shifting procedure. If done by hand, it only requires one extra step, but it can also be done in VBA. The same works for rows.

Leave a Reply

Your email address will not be published. Required fields are marked *

15 − 4 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.