Advanced Custom HLOOKUP Formula

About a year ago I posted an explanation how to create an advanced custom vlookup formula. Recently, I had a reader ask me how to convert this custom code into an advanced custom hlookup formula. It's not as easy as changing all the columns to rows and vice versa as the offset function needs to also be applied. Here is the custom hlookup VBA code:

Public Function HlookupNth(MyVal As Variant, MyRange As Range, Optional RowRef As Long, Optional Nth As Long = 1)
Dim Count, i As Long, cll As Range
Count = 0
If RowRef = 0 Then RowRef = MyRange.Rows.Count
For Each cll In MyRange.Rows(1).Cells
  If cll.Value = MyVal Then
    Count = Count + 1
    If Count = Nth Then
      HlookupNth = cll.Offset(RowRef - 1).Value
      Exit Function
    End If
  End If
Next cll
HlookupNth = "Not Found"
End Function

And the forumla would have this format:

=HLOOKUPNTH(lookup_value, lookup_Range, col_index_num, nth_value) 


And a reminder: don't forget to get your copy of our Super Bowl squares spreadsheet before the big game on Sunday.