Lookup Nth Value in Excel 2016

[Jamesbutt84]

Hi all,

I have been attempting to find a good way to perform a vlookup to return up to 14 results for the same lookup value and have been trying a number of possible combinations of INDEX & MATCH formulas, but found the results to be inconsistent (possibly through my own error) so settled on the below VBA code for LOOKUPNTH:

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

This has done the trick nicely, but it is slowing my system up immensely when it is trying to do the calculations (often 20 minutes to half an hour to caluculate!)

Does anyone know either how to optimise the above or able to provide a better solution for this?

Any help would be greatly appreciated!

Many thanks,
James

 

[systematical88]

Hopefully I am understanding correctly what you are trying to do and not oversimplifying it. But wouldn't this be as easy as setting up 3 columns (with the first two being your intended lookup value and the second being nth position) and then in the third column a large if formula based on the two values (if you have a version of excel prior to 2016) but if you have 2016 and newer then could you could use the AGGREGATE function to do the same thing (I found this out from a board member in another thread just recently).

 

[JoeMo]

I think you could get a big speed boost by converting the table_array range to a 2-D variant array like this:

Dim V as Variant
V= table_array.Value

Then do all the subsequent processing (For nRow = 1 To .Rows.Count - Next nRow) in memory (much faster) like:

For i = LBound(V,1) to UBound(V,1)
      If V(i,1) = lookup_value Then
          nval = nval +1
'rest of code

 

[Jamesbutt84]

Hi JoeMo,

Thanks very much for your reply

I think boosting speed of the operation would be the way to go and have attempted to implement the changes you have requested, but must admit, my VBA knowledge is quite limited and now get a #NAME error when I perform the calculations. Would you be so kind as to show me where I have gone wrong?

Function VLOOKUPNTH(lookup_value, table_array As Value, _
    col_index_num As Integer, nth_value)
     ' Extension to VLOOKUP function.  Allows for finding
     ' the   "  nth  "   item that matches the lookup value.
    
    Dim V As Value
    Dim nVal As Integer
    Dim bFound As Boolean
    VLOOKUPNTH = "Not Found"
    With table_array.Value
    V = table_array.Value
        For i = LBound(V, 1) To UBound(V, 1)
      If V(i, 1) = lookup_value Then
          nVal = nVal + 1
    End With
End Function

Apologies if (as I suspect) I have coc*ed it up!

Many thanks,
james

 

[Jamesbutt84]

Hi systematical88, thanks for your help with this! I did try different uses of the LARGE formula before going on to the INDEX/MATCH ones but found that some of the results which pulled back (I have up to 14 potential results for one lookup) started becoming incorrect in some of the latter instances when compared against the data. Being honest though, that was likely my error rather than a problem with the formula! I will see if I can improve the VBA, but try the AGGREGATE function if that still proves to be quite slow in calculating. Thanks again

 

[JoeMo]

Hi JoeMo,

Thanks very much for your reply

I think boosting speed of the operation would be the way to go and have attempted to implement the changes you have requested, but must admit, my VBA knowledge is quite limited and now get a #NAME error when I perform the calculations. Would you be so kind as to show me where I have gone wrong?

I need some information to help you.
Is the col_index_num the column number in the range table_array where the lookup return value resides or is it an absolute column number. In other words suppose table_array is the range C10:X100 and the return value should come from column H, then is col_index_num = 6 or is it = 8?

 

[Jamesbutt84]

Hey JoeMo, Thanks again for your reply! The col_index_num should be a range and I had left that in the VBA as a range initially, but it was not letting me execute the code without changing that to a value. An example of the VLOOKUPNTH formula I am using would be =VLOOKUPNTH($C17,'Sheet1'!$S:$U,3,2). Hope this helps and thanks again for your assistance, it is a great help!

 

[JoeMo]

That doesn't answer the question I posed so I'm going to assume col_index_num is the column within range table_array. Please, copy the code below directly from your browser and paste it as a standard module so it's just as provided here. Let me know if it works, and, if so, if it calculates faster than the code you posted in post #1 .

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 V As Variant
    Dim nVal As Long
    V = table_array.Value
    For i = LBound(V, 1) To UBound(V, 1)
        If V(i, 1) = lookup_value Then
            nVal = nVal + 1
            If nVal = nth_value Then
                VLOOKUPNTH = V(i, col_index_num)
                Exit Function
            End If
        End If
    Next i
VLOOKUPNTH = CVErr(xlErrNA)
End Function

 

[Jamesbutt84]

Morning Joe, apologies, I did realise that after I had replied, but couldn't edit my message! Your assumption was correct though and the updated code has made a big difference to the calculation times, but it is still quite slow. Do you think there may be more optimisation that could be carried out on the code? Thanks again for all your help so far, it is very much appreciated!

 

[JoeMo]

Morning Joe, apologies, I did realise that after I had replied, but couldn't edit my message! Your assumption was correct though and the updated code has made a big difference to the calculation times, but it is still quite slow. Do you think there may be more optimisation that could be carried out on the code? Thanks again for all your help so far, it is very much appreciated!

Can you be a bit more quantitative? Your OP said 20-30 minutes to calculate with the function you posted there. For the same dataset, how long does it take to calculate using the function from post #8 ?