Lookup and Rank(?)

[joe90]

Hi

I have a large data set containing survey results at a set number of sites.
The surveys are done periodically so the dataset contains multiple rows for each site, the date the survey was done being the variable.

I want to be able to create a list of all sites and lookup from the dataset just the latest survey result.

Is this possible?

Thanks

[Richard Buttrey]

Yes indeed - and welcome to the forum.
Probably the easiest of no doubt several ways is as follows. It assumes the Site references are in col A, dates in col B and results in col c. Make sure the list is sorted by primary column A, and secondary column B in ascending order. Now create your unique list of sites in col D and eneter the following in E1 and copy down.

Please Login or Register  to view this content.

Adjust column references as necessary for your data.

HTH

[joe90]

Thanks RB, works well.

[joe90]

I have discovered a slight flaw in this that is corrupting my results:

If a site in my list hasn't had a survey the above formula returns the value from the site above it in the list.

Is there a way to stop it returning a value if there hasn't been a survey?

Cheers

[Domenic]

Try...

=IF(LOOKUP(D1,A2:A100)=D1,INDEX(C2:C100,MATCH(D1,A2:A100),1),"")

or

=IF(LOOKUP(D1,A2:A100)=D1,LOOKUP(D1,A2:A100,C2:C100),"")

Hope this helps!