Return the value in one range based on the relative position of a value in another range with this Excel user-defined function. For some applications, this is a lot simpler than using a combination of the index, match, offset and/or lookup functions.

It’s especially useful as a replacement for the Index-Match-Match formula combination when working with relative positions of ranges.

Return Value Relative to Another Range

Function RangeMatch(ByVal xy As Range, ByVal array1 As Range, array2 As Range) As Variant
'******************************************************************************
'***DEVELOPER:   Ryan Wells (wellsr.com)                                      *
'***DATE:        03/2017                                                      *
'***DESCRIPTION: Return the value in one range based on the relative position *
'***             of a value in another range. For best results, array1 and    *
'***             array2 should be entered with fixed references via the use   *
'***             of dollar signs, like $A$1:$E$10.                            *
'***INPUT:       xy - A single cell within range array1.                      *
'***             array1 - The range where xy exists within.                   *
'***             array2 - The range you want to look up to find the value in  *
'***                      the cell corresponding to the position of xy inside *
'***                      array1. The size of array1 and array2 must match.   *
'***OUTPUT:      The output will be the value in array2 that is in the same   *
'***             position as xy is within array1. This is a simpler form of   *
'***             one of the dozens of index/match/lookup/offset Excel formula *
'***             combinations.                                                *
'***EXAMPLE:     =RangeMatch(D10,$B$9:$E$11,$B$3:$E$5)                        *
'******************************************************************************
  
'------------------------------------------------------------------------------
'I. Preliminary Error Checking
'------------------------------------------------------------------------------
'Error 0 - catch all error
On Error GoTo RangeMatchError:
'Error 1 - xy more than 1 cell selected
If IsArray(xy) = True Then
    If xy.Count <> 1 Then
        RangeMatch = "Too many cells in variable xy (argument 1). xy should be 1 cell."
        Exit Function
    End If
End If
 
'Error 2 - array dimensions aren't even
If array1.Count <> array2.Count Or _
   array1.Rows.Count <> array2.Rows.Count Or _
   array1.Columns.Count <> array2.Columns.Count Then
    RangeMatch = "Lookup arrays are different dimensions (arguments 2 3)."
    Exit Function
End If
 
'Error 3 - Cell xy is not positioned inside array1.
If Application.Intersect(xy, array1) Is Nothing Then
    RangeMatch = "Cell xy (argument 1) does not reside inside array1 (argument 2)."
    Exit Function
End If
 
'------------------------------------------------------------------------------
'II. Return Position Inside Other Range
'------------------------------------------------------------------------------
If Not Intersect(array1, xy) Is Nothing Then
    RangeMatch = array2.Cells(Range(array1(1), xy).Rows.Count, _
                              Range(array1(1), xy).Columns.Count)
End If
 
Exit Function
'------------------------------------------------------------------------------
'III. Final Error Handling
'------------------------------------------------------------------------------
RangeMatchError:
    RangeMatch = "Error Encountered: " & Err.Number & ", " & Err.Description
End Function

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

How to use the RangeMatch UDF

Once you’ve copied and pasted the above macro into a module in your VBA editor, you can begin using the formula.

The formula accepts 3 arguments:

  1. xy - A single cell within range array1.
  2. array1 - The range where xy exists within.
  3. array2 - The range you want to look up to find the value in the cell corresponding to the position of xy inside array1.

The size of array1 and array2 must match. The output will be the value in array2 that is in the same position as xy is within array1. This is a simpler form of one of the dozens of index/match/lookup/offset Excel formula combinations. I know this still sounds confusing, but bear with me!

This macro requires you have 2 related tables with identical dimensions. Here are a few examples of how you can find yourself in this situation:

  • One table can be prices and the other quantity.
  • One table can have investment types and the other investment returns.

Whatever your layout, you would enter a formula like the one below:

=RangeMatch(D4,$B$2:$F$6,$H$2:$L$6)

The dollar signs ($) are important because it allows you to fix the ranges while dragging your formula across multiple rows and columns, like in this screenshot:

RangeMatch Excel Function to Replace Index Match Match or Offset

The function will return the value in the second range corresponding to the position of the cell inside the first range.

It's more useful than it looks!

I know the above example is a little silly, but it makes more sense when you need to manipulate the data based on the value in the second range. For example, suppose you need to lookup a 3rd or 4th value based on the value in your second range and the value in your second range can change as a function of time or position. This is what happened to me when I developed this macro.

I developed this macro for a specialized nuclear application so there’s a good chance you may not find it useful. That’s okay! If only one of you finds it useful, that makes it worth the post.

I’m sure some of you are thinking “why couldn’t you just do…?”. The answer is, I could have! There are many ways to reach the same answer in Excel. Truth is, I needed this formula to make the formulas more readable in my particular application. I was having a hard time following the popular Index-Match-Match formula combination for my spreadsheet with dozens of tables.

By shortening the length of the formula and reducing the number of arguments and parentheses, I was better able to follow what was going on with the formula. More importantly, so could my client.

As an example, RangeMatch made the following formula far more readable by changing:

=IF(AG41="","",ROUNDUP((INDEX($B$3:$O$16,MATCH($AU41,$B$3:$B$16,0),MATCH(AV$23,$B$3:$O$3,0)))/VLOOKUP(AG41,$T$5:$Y$6,3)*100,0))

to this:

=IF(AG41="","",ROUNDUP(RangeMatch(AG41,$AG$41:$AS$53,$C$4:$O$16)/VLOOKUP(AG41,$T$5:$Y$6,3)*100,0))

The second formula isn’t that much shorter, but it has far fewer arguments. This makes it a lot easier on the eyes and simpler to develop when you have to drag formulas across a number of rows and columns.

Occassionally I’m asked how people can help keep the VBA Tutorials Blog online. The best thing you can do is purchase the add-ins on my Excel Add-ins page. This is my primary source of blog revenue and the money I receive from sales helps pay to keep this website online and accessible for all to learn.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.