Left Lookups: How to avoid the limitations of Vlookup

You can avoid the limitations of the Vlookup function by using 'left lookups'. Vlookup, as you already know, can only look up values to the right of the defined value.
In the beginning the combination formula for a left look up may seem formidable but a closer look reveals its simplicity. Functions like address and match are nested into the indirect function! Now you can look up values to any columns on the left of the value to be looked up.