If you are like me, you have a few excel spreadsheets that are becoming larger by the day. It can become very complex to keep up and you will often use functions such as the “match” function that we discussed last week. Another function that can help when you are using reference functions is the offset function. We used it in last week’s post but it was done rather quickly. Here is a better example:
As you can see, if each product has 2 regions, how could you find what those are and what the prices are? A vlookup would not work because of the fact that the result is on a different line. In order to proceed, we will use the match function but also the offset one.
Here is the formula for the offset function:
=OFFSET(reference, rows, cols, height, width)
In this case, we will need to find “orange” through the match function and then use the offset to find both locations. This could work on a much larger excel.
Where is it? =MATCH(“orange”,A:A,0)
Location #1 =OFFSET(A1,MATCH(“orange”,A:A,0),1)
Location #2 =OFFSET(A1,MATCH(“orange”,A:A,0)+1,1)
When you are unsure about how a file will evolve, if columns or ranges will be moved around, using the match or offset function will make it much easier to maintain the files over time.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
