Why is the INDEX-MATCH function combination often preferred over VLOOKUP in spreadsheets for complex or robust lookup tasks?

The INDEX-MATCH function combination is often preferred over VLOOKUP in spreadsheets for complex or robust lookup tasks primarily due to VLOOKUP's inherent design limitations. VLOOKUP, which stands for "vertical lookup," is designed to search for a value in the leftmost column of a specified table array and return a corresponding value from a column to the right of the lookup column within that same table array. Its third argument, `col_index_num`, requires a fixed numerical position of the column from which to return a value. This design imposes two significant constraints that INDEX-MATCH overcomes. First, VLOOKUP can only perform a lookup to the right of its lookup column. If the desired return value is located to the left of the column containing the lookup value, VLOOKUP cannot accomplish the task directly without re-arranging the data, which is often impractical or undesirable. In contrast, INDEX-MATCH provides bidirectional lookup capabilities. The INDEX function returns a value or the reference to....