PDA

View Full Version : Solved: vlookup:Search from bottom row instead of top row



TheAnswer
12-12-2010, 07:03 PM
Set subValue = Workbooks("bbca volume report 2010_may_team.xls").Worksheets(sheetActive).Columns("A"). _
Find(What:=subArea, After:=Columns("A").Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

With this, i am able to search for value in a column from top row to bottom row. But is there a way to search from the bottom row to the top row instead?

austenr
12-12-2010, 07:43 PM
This will return the last match, assuming your lookup value is in A1 and your return results are contained in column D....change as necessary.

=LOOKUP(2,1/($C$1:$C$5201=A1),$D$1:$D$5201)

kroz
12-13-2010, 06:39 AM
not tested:


A = Range("A:A"). _
Find(What:=subArea, Before:=Range("A65550"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)

TheAnswer
12-13-2010, 06:12 PM
Thanks to both of you:)
Kroz: It worked!!