Can users perform a reverse lookup

[Blue Max]

Two questions regarding the lookup functions as follows:

FIRST, is there any way to configure Lookup, Hlookup, Vlookup, or Match so
that the array will be searched from end to beginning (reverse order) rather
than from beginning to end?

SECOND, is there a way to configure a lookup so that the user can search for
the criteria in something other than the first row or first column of a
range? For example, could the user lookup a value in the third row and
return the associated value in the first row?

Thank you for any help with these questions.

 

[Fred Smith]

FIRST, no. If you're searching for an exact match (4th parameter of False),
it doesn't matter what order the array is in. But if it needs to be in order
(4th parameter True), and you want to search in reverse, you would need to
sort the array first.

SECOND, not with Vlookup, but Index/Match can be used as an alternative. For
example:
=INDEX(A:A,MATCH("Value",C:C,0))
will search column C and return the corresponding row entry in column A.

Regards,
Fred.

 

[Gary''s Student]

To search in reverse order means you will find the LAST occurance of the item
search rather than the first. Say we want to find the last occurance of 13
in A1 thru A100:

=MATCH(13,1/(A1:A100=13))
This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.


This is related to your second question. Say we want to lookup something in
column C and return the equivalent column A value.

Use MATCH() to find the proper row in column C and then use OFFSET() to get
the proper value in column A.

 

[Blue Max]

Dear Gary's Student,

INGENIOUS! Thank you very much. Your example also opens the door to many
other possibilites. We did not realize that you could perform functions on
the "Lookup_Array" argument. Alas, we can understand the theory, but are
still a little perplexed at how this configuration actually works.
Evaluating the formula doesn't prove very helpful either - it looks as if
there is no #13 to match in the lookup_array. Can you explain a little more
in depth why this formua works?

Thank You,

Richard

 

[Blue Max]

Thank you, Fred, some excellent suggestions. You may also want to look at
the solution suggested by "Gary's Student" in this same thread. They have
also successfully solved the first problem in a very ingenious fashion,
without having to physically sort the array.

Thanks,

Richard

 

[Blue Max]

Hello Again,

Just noticed a little hickup in your suggested solution. The formula works
well with numbers, but I cannot seem to get it to work with text. Is there
a variation that might work with text entries for both the match value and
the lookup_array?

Thanks,

Richard

 

[T. Valko]

To lookup the *last* instance of "x" in column A and return the
correspinding value from column B:

=LOOKUP(2,1/(A1:A100="x"),B1:B100)

 

[bcw_now]

To answer your 1st question (reverse vlookup) the only success that i have
had is a little complicated (if there is a shorter way, please post). It
includes the following functions: countif, match, and index functions.

1. You need to count the number of times (separate cell) your variable
appears in the array column. Eg looking for how many times "13" shows up in
column A "=countif(A:A,13)".

2. I have a column (X column) dedicated to the number of times 13 shows up.
So if 13 shows up 5 times, it designates 5 rows.

3. Next to the designated column I have a match function and within the
match function i have an index function nested within (Y column). 2 parts
here.

a. Along the Y column I designate a counter column (Z column) that will
add 1 to the row number.

b. Back to the Y column, you will input a match function that will locate
the next time 13 pops up. And within the match function is nested an index
function that will reference the start lookup row to the end of the column.
"=match(13,index(a:a,counter or row+1):index(a:a,end of counter
column),0)+current row)"

Columns X Y Z
# of times 13 Row # Counter (row+1)

Not exactly a reverse vlookup but it will give you the last time the
variable popped up.

Hope this helps