Suppose you have a 1-2-3 worksheet like the one illustrated. You want to enter an employee number into Cell B9 and have the formula in Cell B10 return the corresponding employee name. Your first instinct might be to try the @Vlookup function, but @Vlookup requires the first column of the range to contain the value to be looked up. In our example, the lookup value is in the fourth column.

Caption: 1-2-3's @Vlookup function works only if the lookup value is in the first column of a range

You could rearrange the columns, but an easier way to get the job done is to use an @index formula instead. Just enter the following formula in Cell B10:

The @match function returns the relative position of the employee number in Column D (with row numbering beginning at 0). The result of this function is then used as the third argument for the @index function, which returns the cell at a specified column and row position within a range.

The formula will also work in Quattro Pro, but you'll have to make the following slight adjustments, since the arguments for Quattro Pro's index function appear in a different order, and cell and row numbering begins with 1, rather than 0: