Excel VLOOKUP where the key is not in the first column

Question

"the location of the lookup value in the range. Keep in mind that for VLOOKUP to function properly, the lookup value must always be in the range's first column. For instance, if cell C2 has the lookup value, your range should begin with C."

However, there are moments when I wish I could perform dual-direction lookups. I.e., use a key in column A to lookup the value in column B AND, at the same time, lookup the value in column B to get the value in column A in other calculations.

The only way I'm aware of is to include a column C that exactly duplicates column A, then use AB for the first lookup and BC for the second lookup. But there must be a better option. Is there a method to compel VLOOKUP to look up the key value in a column other than the initial one, or is there another function that can do the same thing?

Answer 1

INDEX/MATCH will do it in any direction of search.

So for your example of B --> A:

=INDEX(A:A,MATCH(yourCriteria,B:B,0))

The MATCH returns the matching row's number. It's optional to use the third Criterion of 0. When it comes to the fourth VLOOKUP criterion, which searches for an exact match, the value 0 is equivalent to FALSE. The match that is less than or equal to the criteria, like VLOOKUP's TRUE, will be returned if the data is sorted and the default value is 1.

The INDEX determines the right value from that and returns it.