Is there a way to get these functions to work in both directions, meaning
both left to right and right to left for VLOOKUP for instance?

Example:
I have these three columns:

Rank Name Sales
2 Jones 15
1 Smith 20
3 Brown 10

If I want to create a view in another part of the spreadsheet showing the
Name and Sales of the top ranked person, I can use the normal VLOOKUP
function such as VLOOKUP(1,$A$2:$C$4,2,FALSE) etc. However, if I also want
to be able to show the rank and name of anyone selling more than 12, I can't
just use the VLOOKUP function without also repeating the two columns to the
right of the Sales column.

Or is there a way around that?