Could anyone suggest worksheet functions that will allow me to search
UP a column for a given value, starting from a given position in the
column.

The vlookup function always searches down and so always returns the
first match rather than the last. I want to do this without having to
sort the data first.

Thanks

If you want the last occurrence of a lookup value if there are more than one
and you want to return a value from the same row so if the match is A12 and
you want B12 to be returned?

=INDEX(B2:B20,MAX((A2:A20=D1)*(ROW(A2:A20)))-1)

entered with ctrl + shift & enter

the -1 is to offset the number of cells for row 1, in this case the values
start in row2, if they started in row3 it would be -2 at the end, or change
the index range to always start from the first row (B1:B20) in this case

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


wrote in message
oups.com...
Could anyone suggest worksheet functions that will allow me to search
UP a column for a given value, starting from a given position in the
column.

The vlookup function always searches down and so always returns the
first match rather than the last. I want to do this without having to
sort the data first.

Thanks

Why do you want to search upward? If you include the "FALSE" at the end of
your vlookup, it won't matter what manner your data is sorted in. But the
"False" statement means you will only get the "exact" matches...and not
approximations.

" wrote:

Could anyone suggest worksheet functions that will allow me to search
UP a column for a given value, starting from a given position in the
column.

The vlookup function always searches down and so always returns the
first match rather than the last. I want to do this without having to
sort the data first.

Thanks

Here's another one:

=LOOKUP(2,1/(A2:A20="X"),B2:B20)

Returns the value from column B that corresponds to the last instance of
"X".

Biff

"Peo Sjoblom" wrote in message
...
If you want the last occurrence of a lookup value if there are more than
one and you want to return a value from the same row so if the match is
A12 and you want B12 to be returned?

=INDEX(B2:B20,MAX((A2:A20=D1)*(ROW(A2:A20)))-1)

entered with ctrl + shift & enter

the -1 is to offset the number of cells for row 1, in this case the values
start in row2, if they started in row3 it would be -2 at the end, or
change the index range to always start from the first row (B1:B20) in this
case

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


wrote in message
oups.com...
Could anyone suggest worksheet functions that will allow me to search
UP a column for a given value, starting from a given position in the
column.

The vlookup function always searches down and so always returns the
first match rather than the last. I want to do this without having to
sort the data first.

Thanks