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
```

Write better macros in half the time

I see people struggling with Excel every day and I want to help. **That's why I'm giving away my personal macro library for free.** This powerful gift lets you automatically import all *my macros* directly into *your spreadsheets* with just one click.

### 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:

- 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. 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:

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.

If you haven’t already done so, please visit my Excel Add-ins page and see how my products can help you!

If you have questions, I hope you’ll check out my VBA Consulting page. From there, I can answer all your VBA questions.

While you’re here, check out more of my Code Library solutions for other grab-and-go macros. Subscribe to my free wellsrPRO VBA Training Program and share this article with the good folks on Google+, Twitter and Facebook!

#### About The VBA Tutorials Blog

*The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.*