Searching a list in reverse order

[Richard Cook]

I have a need to search a range of cells from the BOTTOM
row toward the TOP row using a Worksheet Function such as
MATCH or VLOOKUP. These Worksheet functions, by design,
search from the top row toward the bottom. The basic
problem is related to the fact that the data in the range
is not in, and cannot be pre-sorted into, ascending or
decending order. I need to find the LAST match in a range
of cells of NON-SORTED data and sorting the data first is
not practical. Anyone know a trick?

 

[Biff]

Hi!

Here's an *UGLY* solution based on a 2 column lookup table.

Assume the following:

The lookup table is in the range A2:B9.
Cell D1 is your lookup value.
In cell D2 enter a formula that gives you the total count
of the number of times the lookup value appears in the
lookup column, in this example, column A2:A9:

="A"&COUNTIF(A2:A9,D1)

Now, enter this formula to lookup the last instance of the
lookup value and return the corresponding value from
column B. Entered as an array - CTRL,SHIFT,ENTER:

=INDEX(B$2:B$9,SMALL(IF(A$2:A$9=D1,ROW(INDIRECT
("A$1:A$"&COUNT(A$2:A$9)))),ROW(INDIRECT(D2))))

This formula uses the value in D2 to determine the last
instance of the lookup value.

If you want to find the Nth instance all you have to do is
enter a number in another cell say, D3, and change the
formuls in D2 to:

="A"&COUNTIF(A2:A9,D1)-D3

Say for example your lookup value is Tom. The formula in
D2 returns 5 meaning there are 5 Tom's in the lookup
table. To find the 3rd instance of Tom in cell D3 you
would enter 2. 5-2=3

If your table is more than a simple 2 column array you'll
have to provide more detail!

Biff

 

[Biff]

Ooops!

Samll correction:

In the first INDIRECT function you may need to change the
COUNT function to COUNTA depending on the type of data
you're looking up. I used a version of this same formula
answering another post and that situation involved numbers.

Biff

 

[Frank Kabel]

Hi
try the following array formula:
=INDEX(B1:B100,MAX(IF(A1:A100=lookup_value,ROW(A1:A100))))

 

[Aladin Akyurek]

=LOOKUP(2,1/(LookupRange=LookupValue),ReturnRange)

LookupRange and ReturnRange must be equal in size & they cannot consist of
whole columns like A:A and/or C:C.