1

My question is based on this one.

In that question, one asks for a kind of search, and I've answered with a VLookup formula. The answer to that was "I would like to make this work in the reverse order", where I thought "That should be easy: just invert the range where VLookup runs through", but it seems not to be that simple:

Let's have a look at following two formulas:

=VLookup(E2;$A$2:$B$6;2;FALSE)
=VLookup(E2;$A$6:$B$2;2;FALSE)

Both formulas are treated as equal by Excel, and in fact, why not: in both cases I mention a rectangle, defined by the cells $A$2, $B$2, $A$6 and $B$6, to run through. So, I can't blame Excel for doing twice the same thing.
However, I mean it in another way: in the first formula, I say "Start at row 2 and go to row 6", while in the second formula I say "Start at row 6 and go down to row 2", but Excel seems not to understand this.

I like to say to Excel

"When I give you a range, don't automatically go from up to down, but follow the direction I've given".

Is this possible?

For your information, I'm not looking for an answer on the question I refer to (using index/match or vlookup/choose or whatever), I'm looking for a general approach on my general question (not only to be used for VLookup, but also for Match or other search functions).

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • 1
    Excel O365 got `XLOOKUP()` and `XMATCH()` to do that. They have a parameter where you can specify to look bottom-up instead of top-down. – JvdV Dec 03 '20 at 13:47
  • 1
    ^^^^ Which would suggest that this is an XY problem... – BigBen Dec 03 '20 at 13:51
  • Well, there are ways to use `LOOKUP()` to retrieve a last match as sort of a reversed `INDEX()+MATCH()`, e.g.: `=LOOKUP(2,1/(A:A="X"),B:B)`. However, I don't think that is categorized under what you describe in your last two sentences. – JvdV Dec 03 '20 at 14:10
  • 1
    For previous Excel versions.. I didn't see how a dummy sheet with transpose function doesn't solve the lookup thingy.. if just reverse direction, `=OFFSET(Sheet1!$A1,100-ROW(),COLUMN()-1)` on Sheet2 A1 cell (assume maximum 100 line data on Sheet1 ), should do. – p._phidot_ Dec 04 '20 at 14:30
  • 1
    @JvdV: please write your comment about `XLookup()` as an answer, I'll accept it. – Dominique Jan 18 '21 at 16:00

1 Answers1

1

As per my comment:

Microsoft365 got XLOOKUP() and XMATCH() to do that. They have a parameter where you can specify to look bottom-up instead of top-down.

Previous versions of Excel do have ways to retrieve the last match, however that is technically not the same as fixing the search direction bottom-up.

JvdV
  • 70,606
  • 8
  • 39
  • 70