Glookup Function - Combining Vlookup and Hlookup

I have been working on a new Excel function of late as I have always liked Vlookup and Hlookup but as discussed in an earlier post the more you try and do excel the less use they become, the issues I was finding more and more is that I used Match and Offset far more often. The main problem I had is that Vlookup will find a value in the left hand column and return a value, and Hlookup finds a value at the top but what if you have a grid and need to select a value based on a V and an H lookup?

In this case you use Match to find the first value and then use Count and Offset to create the dynamic range for the second, this was fine for one calculation but after writing 100 formulas on each sheet it becomes a pain. Therefore I worked on my new function.

Glookup (Grid Lookup) it works on the same lines as V and H lookup only the two lookup values are also the column return counts.

Here is the Formula broken down =Glookup([Vlookup],[Hlookup],[GArray])

Where Vlookup is the value in the Left Hand Column

Hlookup is Value in Top Row

GArray is the Grid Array (Where your data is – Must include your two header rows)

In the above example therefore =glookup("Feb",2007,A2:K14) would have the result of 23

So how does it work? Firstly I have to say in the interest of fairness and because it would simply be wrong not to, I have to say a big vote of thanks to RomperStomper over at Excelhelp.net who helped me with tidying up the code into a very nice succinct module.

Anyway to use Glookup simply paste the following code into the VBA window of your spreadsheet (Pressing Alt + F11 or going to Tools-Macro-Visual Basic Editor)

Function GLookup(VLookup, HLookup, GArray As Range)

Dim VRef

Dim HRef

VRef = WorksheetFunction.Match(VLookup, GArray.Columns(1), 0)

If IsError(VRef) Then

GLookup = CVErr(xlErrValue)

Else

HRef = WorksheetFunction.Match(HLookup, GArray.Rows(1), 0)

If IsError(HRef) Then

GLookup = CVErr(xlErrValue)

Else

GLookup = WorksheetFunction.Index(GArray, VRef, HRef)

End If

End If

End Function

It should now look like this

Now you can call the function from any worksheet in this book, don’t forget though you will unfortunately have to copy the function into each sheet you want to use it on.