Wednesday, February 1, 2012

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.

1 comment:

  1. Intersting and beautiful blog lovely presentation thanks for sharing your views...Excel Support We24support tech team are available 24/7 for repairs on computers, printers, microsoft excel 2010laptops, desktops. Our tech team taken to new heights with our technician’s knowledge and 1-866-978-0799 microsoft excel support


I'd love to hear from you!