Q. When should I use VLookUp and when is it better to use Index and Match?
A. There are a lot of opinions about these functions and their use. Some people swear by one approach or the other. However, good reasons exist to use both. If your data are organized with the Lookup_value in the first column or the first row, a VLookUp or HLookUp, respectively, is perfectly appropriate. If your LookUp column is not sorted in ascending order, be sure to use 0 or False in the Range_lookUp field.
What if your LookUp_value isn’t in the first column? Then the combination of Index/Match is your go-to solution. Match looks up a value in a range and returns its relative position. Index examines a range for a value based on the relative position of the item found in the Match function. In our example, we know the product name (B9), but need the item number.