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 1110 times.

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