Lookup table value based on multicriteria and sorting

**rrbest** · 04-04-2012, 04:15 PM

I have a table from an access database with four fields.

Table!A:A = ID
Table!B:B = Topic
Table!C:C = Date submitted
Table!D:D = Title

The user specifies both the ID (Main!A1)and the Topic (Main!A2)on a seperate worksheet, and from this I would like

Main!A4: The title of the most recently submitted story
Main!A5: The title of the second most recently submitted story
...
Main!A13: the title of the 10th most recently sumitted story

Previously, I have used an excel formula array with Large(IF) structure. However, the excel arrays cause the spreadsheet to function too slowly. Is there a way to implement this through VBA to speed it up? Thanks in advance.

**rylo** · 04-04-2012, 06:15 PM

Hi

How about a UDF.

Main!A4: =myfunc($A$1,$A$2,Table!$A$2:$A$21,ROW()-3)

Please Login or Register  to view this content.

HTH

rylo

**rrbest** · 04-04-2012, 06:51 PM

Originally Posted by rylo

Hi

How about a UDF.

Main!A4: =myfunc($A$1,$A$2,Table!$A$2:$A$21,ROW()-3)

Please Login or Register  to view this content.

HTH

rylo

Thank you for the response. This functions exactly how I would like it to. Unfortunately, like arrays, I am finding it to be slow to calculate (28 seconds). I wonder how much work really goes in to determine the result for this type of problem. This UDF is great, and I wonder if there is any way to improve the speed.

**rylo** · 04-04-2012, 07:19 PM

Hi

1) How long did it take the formulas to recalc? I'm just wondering if there was any significant time saving (if at all).

2) Can you put up a sample file with a reasonable chunk of data and the UDF (or functions if they turned out to be faster). Advise how long it takes to recalc with formulas / UDF on your machine. We can then try it on other machines to see if it is a machine issue, or a data size issue.

rylo