spreadsheet - Excel VLOOKUP where the key is not in the first column

Question

"The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C."

But sometimes I want to be able to do dual-direction lookups. Ie, lookup using a key in column A to get the value in column B AND at the same time, in other formulas, lookup the value in B to get the value in A.

The only way I know is to add a column C which mirrors A, then use AB for the first lookup, and BC for the second lookup. But there has to be some cleaner solution. Is there some way to force VLOOKUP to use a different column other than the first one to find the key value, or is there a different function that would allow the equivalent?

As a side note, I am asking about Excel, but I actually use LibreOffice. Presumably the functions should be identical, but an answer that also works in LibreOffice would be preferable.

Answer 1

INDEX/MATCH will do it any direction of search.

So for your example of B --> A:

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

The MATCH returns the row number of the match. The third Criterion of 0 is optional. The 0 is the same as FALSE for the forth criterion of VLOOKUP, in that it looks for an exact match.

The default is 1 with the data sorted it will return the match that is less than or equal to the criteria Like VLOOKUP's TRUE.

From that the INDEX finds and returns the correct value.

---

With the introduction of the Dynamic Array formula XLOOKUP we can use:

=XLOOKUP(yourCriteria,B:B,A:A,"",0)