Excel gurus gone wild - possible error?

[bmorro]

I am going through this book page-by-page and using the examples to create my own personal file. The copyright on my version is 2009, so this may be a moot point due to updated versions.

On page 53: Looking at the definitions in the formula below I believe the underscore should be eliminated for the formula to work - I had to drop it.

Function VLOOKUPNTH(lookup_value, table_array As Range, _col_index_num As Integer, nth_value)
'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
'Check to see if this is the nth match
If nVal = nth_value Then
VLOOKUPNTH = .Cells(nRow, col_index_num).Text
Exit Function
End If
End If
Next nRow
End With
End Function

 

[RoryA]

In the book the declaration line is on two lines, which is why the line continuation character (underscore) is there. So it's either:

Function VLOOKUPNTH(lookup_value, table_array As Range, _
col_index_num As Integer, nth_value)

or:

Function VLOOKUPNTH(lookup_value, table_array As Range, col_index_num As Integer, nth_value)

 

[bmorro]

Rory, I feel silly.

I was trying to condense the code and I missed this syntax rule.

Thank you for pointing it out.

 

[generalDisarray]

Don't know if i should reply to this or start a new thread with my comment. But, i have a comment on that same section of the book (Nth Vlookup challenge from Guru's Gone Wild)

My comment is this, you can do it with SUMPRODUCT, INDEX, and the SMALL function. Actually, the solution seemed to be a very logical extension of the previous sections, was surprised it wasn't in the book after I got it figured out.

Example solution attached.

---
Basic solution (please see the attachment for the actual, in context solution). You can enter an 'n' value in the red box (L3) and watch the matched values populate in the K columns. (E&F were just me figuring things out, K is the 'money' solution)

CSE, or Array function solution (confirm with Cntrl+shift+enter, don't type the curly braces)

{=IF(nValueHere > SUMPRODUCT (IF(keyCol=SomeKeyToLookup,1,0)*IF(keyCol=SomeKeyToLookup,1,0)),"OOB",INDEX(scores,SMALL(IF(keyCol=SomeKeyToLookup,ROW(keyCol)-HeaderRowNumber,FALSE),nValueHere),1))}

I have a solution worked out but can't post attachements(but i can here)