Excel

VLOOKUP for Duplicates - called VLOOKUPnth and HLOOKUPnth

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

petermoran

Description:

These two Excel Functions replace VLOOKUP and HLOOKUP where you need to do a lookup and there are duplicates present. VLOOKUP and HLOOKUP only find the FIRST matching entry, this allows you to specify which entry. 

Discussion:

If a lookup table is based on dates and the dates may have duplicates, with VLOOKUPNTH you can select each entry for a particular date by nominating in each VLOOKUPNTH which entry you wish to find, such as the third or fifteenth one in the list. Format: VLOOKUPNTH(lookup_value, lookup_Range, col_index_num, nth_value) Format: HLOOKUPNTH(lookup_value, lookup_Range, col_index_num, nth_value) 

Code:

instructions for use 

			
Function VLOOKUPNTH(lookup_value, table_array As Range, _ 
    col_index_num As Integer, nth_value) 
     ' Extension to VLOOKUP function.  Allows for finding
     ' the   "  nth  "   item that matches the lookup value.
     
    Dim nRow As Long 
    Dim nVal As Integer 
    Dim bFound As Boolean 
    VLOOKUPNTH = "Not Found" 
    With table_array 
        For nRow = 1 To .Rows.Count 
            If .Cells(nRow, 1).Value = lookup_value Then 
                nVal = nVal + 1 
            End If 
            If nVal = nth_value Then 
                VLOOKUPNTH = .Cells(nRow, col_index_num).Text 
                Exit Function 
            End If 
        Next nRow 
    End With 
End Function 
 
Function HLOOKUPNTH(lookup_value, table_array As Range, _ 
    Row_index_num As Long, nth_value) 
     ' Extension to HLOOKUP function.  Allows for finding
     ' the   "  nth  "   item that matches the lookup value.
    Dim nCol As Long 
    Dim nVal As Integer 
    Dim bFound As Boolean 
    HLOOKUPNTH = "Not Found" 
    With table_array 
        For nCol = 1 To .Columns.Count 
            If .Cells(1, nCol).Value = lookup_value Then 
                nVal = nVal + 1 
            End If 
            If nVal = nth_value Then 
                HLOOKUPNTH = .Cells(Row_index_num, nCol).Text 
                Exit Function 
            End If 
        Next nCol 
    End With 
End Function

How to use:

  1. Open your workbook.
  2. Hit Alt+F11 to open the Visual Basic Editor (VBE).
  3. Choose Insert-Module and paste the code above into the code window at the right of the VBE. (You may also want to install in your Personal.xls so you have it available for any workbook.)
 

Test the code:

  1. Like any Excel formula/function, you must use it in cell(s) in the workbook, so you can use it exactly as a replacement for VLOOKUP and HLOOKUP, such as =VLOOKUP(lookup_value, lookup_Range, col_index_num, nth_value).
  2. To call it from your Personal.xls file, write the formula as =PERSONAL.XLS!VLOOKUPNTH(lookup_value, lookup_Range, col_index_num, nth_value).
 

Sample File:

LookUpNextFunction.zip 12.34KB 

Approved by mdmackillop


This entry has been viewed 1114 times.

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