![]() |
#3
|
|||
|
|||
![]()
Here is a custom function that may be of assistance. Note that although I have done a little bit of testing, it has not been tested extensively, so please review the code and test that the function works as expected before depending on it!
Code:
Public Function vlookupx(lookup_value As Variant, table_array As Range, col_index_num As Integer, range_lookup As Boolean, occurrences As Integer) As Variant 'Declare variables Dim i, count As Integer Dim ivalue As Variant On Error Resume Next 'Protect against erroneous inputs If col_index_num > table_array.Columns.count Then vlookupx = CVErr(xlErrRef) Exit Function End If If occurrences < 1 Then vlookupx = CVErr(xlErrNA) Exit Function End If If range_lookup = True And lookup_value < table_array.Cells(1, 1).value Then vlookupx = CVErr(xlErrNA) Exit Function End If 'If range_lookup = true then assume first column is sorted ascending 'Finds largest value that is smaller than lookup_value if lookup_value not found If range_lookup = True Then count = 0 For i = 1 To table_array.Rows.count ivalue = table_array.Cells(i, 1).value If ivalue > lookup_value Then vlookupx = table_array.Cells(i - 1, col_index_num).value Exit Function ElseIf ivalue = lookup_value Then count = count + 1 If count = occurrences Then vlookupx = table_array.Cells(i, col_index_num).value Exit Function End If End If Next i vlookupx = table_array.Cells(table_array.Rows.count, col_index_num).value Exit Function 'If range_lookup = false then look for exact matches Else count = 0 For i = 1 To table_array.Rows.count ivalue = table_array.Cells(i, 1).value If ivalue = lookup_value Then count = count + 1 If count = occurrences Then vlookupx = table_array.Cells(i, col_index_num).value Exit Function End If End If Next i vlookupx = CVErr(xlErrNA) Exit Function End If vlookupx = CVErr(xlErrNA) End Function This function could be used in your example spreadsheet as follows =vlookupx(B1,ULD!$A:$D,2,0,COUNTIF($A1:B1,B1)) Hope that helps. |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
ExecAssist | Mail Merge | 1 | 02-27-2012 04:06 PM |
Table doubles up in MS Word 2010 but not in 2003 | seguinju | Word | 3 | 02-24-2012 11:07 PM |
![]() |
b0x4it | Word | 4 | 06-14-2011 10:01 PM |
Is it possible for Excel print consistently with any printer? avoid awkward page brea | William C | Excel | 1 | 12-21-2010 01:40 PM |
How do I avoid Hourglass using API Function? | sauerj | Word | 1 | 11-10-2010 06:53 AM |