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.

Vlookup is Dead, Long Live Flexible Lookup

When I first found Vlookup I was over joyed with the simplicity and beauty of a string of IFs and Vlookups. I even claimed grandly that there was nothing that could not be done with these two friends of Excel users. However there is a very good chance that I have used my last Vlookup!

The reason I could turn away from my most loyal of Excel functions, the reasons are three fold:

Match

Offset

Indirect

I will demonstrate how I use them by using examples from a spreadsheet I have to calculate your BMI range from your weight (Weights have been changed to protect the Cake Loving.)

Match will return the position of a value in a list e.g. =MATCH(J30,A1:A24) will locate the position of J30 in this case the height in the range A1:A24 the answer is in this case 17 (Which is also the Row number)

Indirect lets you use the result of a Formula as a Cell Reference e.g. INDIRECT("A"&Match(J30,A1:A24)&":R23") As you can see from the attached function arguments this presents itself as a range from A17:R23 now if you change the value in J30 say to 70 you will now get the Dynamic Range A16:R23 and so on.

Offset will return a value a specified number of rows from a given point. e.g. =OFFSET(A1,3,2) will give you the value found in Cell C4. obviously this is not as easy as writing =C4 but if we combine all three functions

=OFFSET(INDIRECT("A"&MATCH(J30,A1:A24)),24-MATCH(J30,A1:A24),5)

Then we have an alternative to Hlookup (In this example I have just entered a 5 in the Rows criteria however for a truly dynamic range you would use another Match() )

So This Function is basically looking up the row the data in J30 is in and then returning the value which is at the bottom of the grid in the specified column. In this case "OK Weight"

Another good use for the Offset function is to create a dynamic named range to automatically update graphs =OFFSET(Daily!$AQ$2,1,1,COUNT(Daily!$K$2:$K$191),1) This function creates a range that starts in AQ2 and is 1 column wide by however many rows deep have data (In Column K) But I may write more about that at a later date.