Offset lookup

I'm trying to achieve a lookup returning data which is somewhat irregular. I've attached a sample which explain the position better (if everyone isn't watching Superbowl)...

What is the result you're looking for ?

Are you looking for last inspection date per property - if so - where are these values to be placed ?

Hi Bob,

Using a helper column D, in D8 enter

Please Login or Register  to view this content.

and copy down
in E8 as an array formula (enter with Ctrl Shift Enter)

Please Login or Register  to view this content.

and copy down.

What's all this about superbowl then? I hope you're not forsaking cricket to watch

Regards

Originally Posted by BB

I am reading the data into another workbook by looking up the propoerty ID and then trying to report on the last date that property was inspected.

I can't change the format which lists the property ID, properties' address or inspection dates as that comes from a data dump.

If by "last" you mean "most recent" date then given the above I'd be inclined to break up the calculation for sake of simplicity... eg:

For sake of demo. let's assume we're using the same workbook but a different sheet (you should be able to adapt to a different workbook).

Using Sheet2 as results...

Please Login or Register  to view this content.

should you change A2 to another ID you should get a different output...
or if preferred simply copy B:D down and enter different IDs in A3 onwards.

Originally Posted by BB

I don't understand the use of the reference in the formula in D2 to SHeet1$C$30. Given the data I posted, this refers to the last date in column C. But if the dump varies in the number of lines, how do I pick up the last date?

Given none of the formulae used in this approach are "overly" expensive you could use a cell reference that's sufficiently large that it will always encase all data (eg $C$10000)

If you do wish to use only the used range you could store the last row of numeric data in another cell and refer to it in the INDEX (or use a Defined Name to store this value).

For sake of demo - assume we place the row number of the last row in Sheet2!A1:

Please Login or Register  to view this content.

The formula in D2 onwards can be adjusted to utilise the above value rather than refer to C30 explicitly:

Please Login or Register  to view this content.