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.

The Joys of INDIRECT Excel Function

I have just been working on a spreadsheet and have finally solved an issue that has bugged me for a while. I know this has been quite obvious but I have been very slow where this is concerned.

If you want to select a total from each sheet in a workbook you can easily select it using the INDIRECT function.

If you have a spreadsheet containing data on each page, called January, February etc... and you want to make a summery sheet you can use this formula.

=SUM(INDIRECT(L4&"!$H$3:$J$47")) where column L contains your month name and each sheet contains the data you want summed in the grid H3:J47 then when you copy down the month names it will automatically address the range to the correct sheet.

I can't believe I have only just realised this would work.