What is vlookup and what is benefit of it?

If you are a long time Excel user, you may already be very familiar with the VLOOKUP function. It works great when applied to the correct situation, and the syntax is very easy. However, there are the INDEX and MATCH functions which may appear to just mimic the VLOOKUP except with more complex syntax. So why switch?

VLOOKUP Benefits:

Easy syntax

Works great on short lists

VLOOKUP Challenges:

Can only retrieve data columns to the right of the lookup column

Will return erroneous results if the lookup column data elements are not sorted

Can only be used with a single search criteria

Will perform slowly on very large lists

If you insert a column in the lookup list, you will break any of the VLOOKUP functions that reference that list because it uses a static offset to retrieve the column

i.e. VLOOKUP(B4, lookupListReference, 4)

In this example the function is lookup up the specific 4th column over in the lookup list

If a column is added to the list, the VLOOKUP function is still only looking at data column 4, but is now referencing the wrong column