lookup for last value

[freshfruit]

I have a report that is formatted horribly. The rows always increase and there are no unique identifiers to use as lookups. As such, I want to do a lookup on the LAST incident of a name. For example, in row B, there are 50 "Real Estates", I want to lookup the last one and return the value three columns over.

[alansidman]

Try this:

Please Login or Register  to view this content.

[freshfruit]

Thanks Alan, but is there a way to do this with a formula? I have a number of lookups that I need to do.

[Pete_UK]

Can you post some example data in a workbook?

Pete

[FDibbins]

Perhaps use a helper column with countif() in it, then do a search (using index/match) based on a combination of the entry and the qty?

[benishiryo]

hi there. maybe something like this would work:
=LOOKUP(2,1/(B2:B10="real estate name"),E2:E10)

to handle increasing range, make it more than it'll ever reach (not the whole columns for this case though) or consider dynamic ranges and Tables. you can google more for that

[azumi]

Assumed your data in Column A and down:

=IF(COUNTA(A:A)=0,"",INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))),0))
(Array Formula

Azumi

[dropanddrive03]

Looks like you have three options and they all work. This is mine.

=INDEX(A1:D100,MATCH(2,1/(A1:A100=*Cell reference or name reference*)),4)

Assumed:

Real estate name is in column A
Value you want to return is in column D
*You can change the cell reference to a number that will never be reached
*Be sure to enter using CTRL + SHIFT + ENTER, not just enter

Edited for dynamic range:

=INDEX(A1:INDEX(D:D,MATCH(9E+307,D:D)),MATCH(2,1/(A1:INDEX(A:A,MATCH("ZZZZZZ",A:A))=*Cell reference or name reference*)),4)

Same rules apply to this formula, but it's a dynamic range, so you don't need to worry about picking a row number. It will always use the highest row number where there is data.