Excel

Find Column Offset Matches

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Justinlabenne

Description:

Use this udf to select a range of cells and then return all user specified column offset matches separated by commas 

Discussion:

You have a list of duplicate names in Column A and you want to find all the values in column C where the values fall in the same row as the column A name you are looking up. =FINDMATCH(E1,A1:A10,2) Cell E1 contains the name to look for. A1:A10 house the range of names and the third argument 2, is how many columns over from the lookup range (A1:A10) to find matching values. 

Code:

instructions for use 

			
Option Explicit 
Option Compare Text 
 
 '"Option Compare Text" makes searches case-insensitive
 'Remove it, or comment it out to make LookupVal searches "Case Sensitive"
 
Function FINDMATCH(LookupVal As range, rng As range, ColIndex As Integer) As String 
     '   Finds a specified lookup value, then returns
     '   specifed column offset matches seperated by comma
    Application.Volatile True 
    Dim r As range, sMatch As String 
    For Each r In rng 
        If r = LookupVal Then 
            sMatch = sMatch & r.Offset(, ColIndex).Text & ", " 
        End If 
    Next r 
    sMatch = Left(sMatch, Len(sMatch) - 2) 
    FINDMATCH = sMatch 
End Function

How to use:

  1. Open an Excel Workbook
  2. Press Alt+F11 to open the vbe
  3. Go to INSERT > MODULE
  4. Copy code from this board
  5. Paste into the newly added module
  6. Press Alt+Q to return to Excel
 

Test the code:

  1. Create a data table for testing
  2. Put the lookup value in a cell (or in first argument)
  3. In the cell to house the formula:
  4. =FINDMATCH(Cell with lookup, range to find name, # of columns over to return values)
 

Sample File:

FindMatches.zip 7.09KB 

Approved by mdmackillop


This entry has been viewed 268 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express