25

"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.

BrianFreud
  • 7,094
  • 6
  • 33
  • 50

1 Answers1

48

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)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Many thanks! A little more finnicky to get it to work, but it works. :) – BrianFreud Jul 28 '16 at 15:59
  • @Scott Craner, do you have a similar solution for excel table instead of a range? – David Leal Mar 21 '19 at 13:56
  • @DavidLeal that is a new question, please post a new question instead of asking on an old thread. – Scott Craner Mar 21 '19 at 13:58
  • 3
    @ScottCraner I found it using your idea: `INDEX(Table1[A], MATCH(yourCriteria, Table1[B],0)`, where `Table1` is the excel table object and `A`, `B` the name of the first and second column respectively. – David Leal Mar 28 '19 at 18:36
  • @ScottCraner I removed my answer, sorry I didn't realize that at the end of your answer you provided a solution for the new `XLOOKUP` excel function introduced. My apology. – David Leal Sep 30 '22 at 00:30