Excel Training Home

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 a little difficult but a closer look reveals its simplicity. Functions like index and match are nested into each other! Now you can look up values to any columns on the left of the value to be looked up.

How the formula works:

  • The Index function returns a value or the reference to a value from within a table or range
  • The Match function returns the relative position of an item in an array, for example, the row value

This formula overcomes the limitation of Vlookup for data analysis since Vlookup can only find corresponding values for the lookup value which is always in the left-most first column and the data to be found is on the right in different columns in the same row.


Left Lookups