





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

 Open your workbook.
 Hit Alt+F11 to open the Visual Basic Editor (VBE).
 Choose InsertModule 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:

 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).
 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 1050 times.

