Have you ever tried using the ‘MATCH’ formula with a ‘VLOOKUP’?
Most regular users of Excel are familiar with writing a simple VLOOKUP (or HLOOKUP) to return a value associated with something. But what if the value you want moves columns on a monthly basis? Do you modify the vlookup formula each month or use ‘MATCH’ so there is no need for an update? In the example below you can change the date in cell B10 and the vlookup will find the correct monthly price. This type of formula also works very well if producing periodic graphs.
Please feel free to download the example above. I hope you find this post useful.