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.

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.

"Blue Max" wrote in message
...
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.

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

"Fred Smith" wrote in message
...
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.

"Blue Max" wrote in message
...
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.

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.
--
Gary''s Student - gsnu200851


"Blue Max" wrote:

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.

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


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200851


"Blue Max" wrote:

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.

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


"Gary''s Student" wrote in message
...
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.
--
Gary''s Student - gsnu200851


"Blue Max" wrote:

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.

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)

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
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


"Gary''s Student" wrote in
message ...
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.
--
Gary''s Student - gsnu200851


"Blue Max" wrote:

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.

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

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
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


"Gary''s Student" wrote in
message ...
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.
--
Gary''s Student - gsnu200851


"Blue Max" wrote:

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.