Is it possible to make a VLOOKUP-like function that looks at the rows in
reverse order, that is, so that it finds the bottom-most matching cell,
not the top-most one?

I know it can be done in VBA, but any attempt to do so ended up being
awfully slow. Actually, it ended up being about as slow as SUMPRODUCT on
the same dataset, so I conclude that VLOOKUP has some optimization I
don't know of and can't really be replicated in VBA.

BTW, I use an equality matching VLOOKUP. If I could sort my data by the
search key, I could of course answer the question on my own by making a
binary search function, but the column I need to search in is unsorted
and that can't be changed.

If possible, I'd like a solution without extra columns... basically, my
task is:

A B C D
1 Key1 y blah...
2 Key2 n blah...
3 Key3 y blah...
4 ^1 Key1 n blah...
5 ^3 Key3 n blah...
6 ^2 Key2 y blah...
7 Key4 y blah...
8 ^4 Key1 y blah...
9 ^5 Key2 n blah...

The ^N is supposed to be a "link" to the row with the previous occurrence
of the key. It is easy to find the FIRST occurrence in the row...

Actually, I want both that "link" and a check if the value in the C
column is the same (for each key in B, the value in C is required to be
the same). If this were SQL, I would of course make an extra B - C
mapping worksheet, but that substantially hinders data entry in Excel,
which is why I instead want to show a consistency check while typing -
but that can be an ordinary VLOOKUP - however, I also want a link to the
previous instance of the same key to "walk up" the texts in D. A "next"
link OTOH is a normal VLOOKUP again...

speaking of links, is it possible to make a clickable hyperlink in a
cell that sets the cursor elsewhere, and not just showing a data item
ID?

Is it possible to make a VLOOKUP-like function that looks at the rows in
reverse order, that is, so that it finds the bottom-most matching cell,
not the top-most one?

Yes

...........A..........B
1........X.........X1
2........Y.........Y1
3........X.........X2
4........X.........X3
5........Y.........Y2

Last instance of X = X3
Last instance of Y = Y2

=LOOKUP(2,1/(A1:A5="X"),B1:B5)
=LOOKUP(2,1/(A1:A5="Y"),B1:B5)

--
Biff
Microsoft Excel MVP


"Marcus Schöneborn" wrote in message
. uni-frankfurt.de...
Is it possible to make a VLOOKUP-like function that looks at the rows in
reverse order, that is, so that it finds the bottom-most matching cell,
not the top-most one?

I know it can be done in VBA, but any attempt to do so ended up being
awfully slow. Actually, it ended up being about as slow as SUMPRODUCT on
the same dataset, so I conclude that VLOOKUP has some optimization I
don't know of and can't really be replicated in VBA.

BTW, I use an equality matching VLOOKUP. If I could sort my data by the
search key, I could of course answer the question on my own by making a
binary search function, but the column I need to search in is unsorted
and that can't be changed.

If possible, I'd like a solution without extra columns... basically, my
task is:

A B C D
1 Key1 y blah...
2 Key2 n blah...
3 Key3 y blah...
4 ^1 Key1 n blah...
5 ^3 Key3 n blah...
6 ^2 Key2 y blah...
7 Key4 y blah...
8 ^4 Key1 y blah...
9 ^5 Key2 n blah...

The ^N is supposed to be a "link" to the row with the previous occurrence
of the key. It is easy to find the FIRST occurrence in the row...

Actually, I want both that "link" and a check if the value in the C
column is the same (for each key in B, the value in C is required to be
the same). If this were SQL, I would of course make an extra B - C
mapping worksheet, but that substantially hinders data entry in Excel,
which is why I instead want to show a consistency check while typing -
but that can be an ordinary VLOOKUP - however, I also want a link to the
previous instance of the same key to "walk up" the texts in D. A "next"
link OTOH is a normal VLOOKUP again...

speaking of links, is it possible to make a clickable hyperlink in a
cell that sets the cursor elsewhere, and not just showing a data item
ID?

»T. Valko« wrote:
Is it possible to make a VLOOKUP-like function that looks at the rows in
reverse order, that is, so that it finds the bottom-most matching cell,
not the top-most one?

Yes

..........A..........B
1........X.........X1
2........Y.........Y1
3........X.........X2
4........X.........X3
5........Y.........Y2

Last instance of X = X3
Last instance of Y = Y2

=LOOKUP(2,1/(A1:A5="X"),B1:B5)
=LOOKUP(2,1/(A1:A5="Y"),B1:B5)

It does work, thanks for that, but why and how?
It seems to look for the number 2 in a vector consisting of 1 and
#DIV/0, so how does it ever find a 2? And why does the ordering not work
if I look for 1 instead, and why does it find nothing at all if I look
for 0.5?

It seems to look for the number 2 in a vector consisting of 1 and
#DIV/0, so how does it ever find a 2? And why does the ordering not work
if I look for 1 instead, and why does it find nothing at all if I look
for 0.5?


Okay, so if Vlookup can't find an exact match, it returns the largest
value less than the target. So there is no way it could return
anything for 0.5. For 2, the next-largest value in the array is 1, and
the function apparently looks through the entire array and returns the
position of the last 1--exactly what is wanted. Looking up 1.1 also
works.

But I cannot figure out why looking for 1 results in the position of
the last 1 and not the first. Why would it not choose the first exact
match it found?

But I cannot figure out why looking for 1 results in the position of
the last 1 and not the first. Why would it not choose the first exact
match it found?

I've often wondered about that myself. I guess that's just how LOOKUP works.
Consider this:

...........A..........B
1........1..........11
2........1..........12
3........1..........13
4........1..........14
5........1..........15

=LOOKUP(1,A1:A5,B1:B5)
=LOOKUP(1,1/(A1:A5=1),B1:B5)

Both formulas return 15.

...........A..........B
1........1..........11
2........1..........12
3........2..........21
4........3..........31
5........4..........41

Both formulas return 12.


--
Biff
Microsoft Excel MVP


"David Hilberg" wrote in message
oups.com...

It seems to look for the number 2 in a vector consisting of 1 and
#DIV/0, so how does it ever find a 2? And why does the ordering not work
if I look for 1 instead, and why does it find nothing at all if I look
for 0.5?


Okay, so if Vlookup can't find an exact match, it returns the largest
value less than the target. So there is no way it could return
anything for 0.5. For 2, the next-largest value in the array is 1, and
the function apparently looks through the entire array and returns the
position of the last 1--exactly what is wanted. Looking up 1.1 also
works.

But I cannot figure out why looking for 1 results in the position of
the last 1 and not the first. Why would it not choose the first exact
match it found?