# Multiple lookups ordered by timestamp

1. ## Multiple lookups ordered by timestamp

Hi,

I have two tables, where one has the rawdata ordered in a vertical fashion and the other shall contain the results ordered in a horizontal fashion.

The problem can be described as follows:

Transfer results from Table 1 to Table 2 by:

For each row in Table2, match either of Phase1, Phase2 and Phase3 with the column Phase1, 2, 3 in Table1.
For each column in Table2, match the Codes with the Codes in Table1.
For each coulmn in Table2, match the Order number for the given Code with the timestamp in columns Created On and Created At in Table1.
The oldest timestamp shall give the lowest order number for the given code.
If there are duplicate timestamps for a given match, both shall be counted with and the first occurrence shall return the first occurring result value and the next one(s) shall return the succeeding result value(s).

Could you please have a look at the attached workbook and see if this can be solved?

Best regards,
Marbleking

2. ## Re: Multiple lookups ordered by timestamp

Please try at J4

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Multiple lookups ordered by timestamp

You need an Array formula using nested Index Small If Row.
=IFERROR(INDEX(\$E\$1:\$E\$27,SMALL(IF(((\$A\$4:\$A\$27=\$G4)+(\$A\$4:\$A\$27=\$H4)+(\$A\$4:\$A\$27=\$I4))*(\$B\$4:\$B\$27=J\$3),ROW(\$A\$4:\$A\$27)),J\$2)),"")

remember to press Ctrl Shift Enter to complete the formula.

4. ## Re: Multiple lookups ordered by timestamp

Another one

=IFERROR(INDEX(\$E:\$E,AGGREGATE(15,6,ROW(\$E\$4:\$E\$27)/MMULT((\$G4:\$I4=\$A\$4:\$A\$27)*(\$B\$4:\$B\$27=J\$3),{1;1;1}),J\$2)),"")

5. ## Re: Multiple lookups ordered by timestamp

@Bo_Ry
I like your AGGRERATE much, but anyway, your solution does not take time stamp in to account
I am working on this and come back soon

6. ## Re: Multiple lookups ordered by timestamp

It quite like a monster, but it works:

``Please Login or Register  to view this content.``

7. ## Re: Multiple lookups ordered by timestamp

These are some very fascinating solutions, y'all! I like the AGGREGATE version too, and that the timestamp was included. Thus, the function will be robust if the columns in Table 1 are sorted differently. Bebo021999's expanded solution seems to be very close, but it actually returns the last result first for each of the Code values. I tried to change from "14 - Large" to "15 - Small" in the AGGREGATE function, but this only resulted in returning wrong results. Could this be looked at?

Also, I unfortunately forgot one important element to consider: There is going to be another criteria column in Table 1 which contains the values "true" or "false" in text format, and only the rows in Table 1 for which this column says "false" shall be included anywhere in Table 2. I hope this doesn't complicate too much.

Thanks for your contributions so far.

Regards,
Marbleking

8. ## Re: Multiple lookups ordered by timestamp

Pls update the sample with new case of sorting table 1 and new column of true/false

9. ## Re: Multiple lookups ordered by timestamp

Hi! Attached you'll find the updated workbook including comments! Regards, Marbleking

10. ## Re: Multiple lookups ordered by timestamp

Please try at L4
Formula:
`Please Login or Register  to view this content.`

11. ## Re: Multiple lookups ordered by timestamp

Thanks Bo_Ry! This looks great; could you please also include the date in the timestamp, as well as time?

Also, if you could update the formula from your first reply in cell L3 for fetching the Codes, such that only Codes from Table 1 for which Phase numbers can be found in Table 2 and for which also the Criterion in Table 1 is "false" will be included, and then sorted across the header row:

``Please Login or Register  to view this content.``
Regards,
Marbleking

12. ## Re: Multiple lookups ordered by timestamp

L4
Formula:
`Please Login or Register  to view this content.`

L3
Formula:
`Please Login or Register  to view this content.`

Confirm by Ctrl+Shift+Enter
or COMMAND+RETURN for Mac

13. ## Re: Multiple lookups ordered by timestamp

Thanks, Bo_Ry!

Having looked more closely at the dummy data that I pasted in Table 2, I now see that I made an error: The values in range S8:S11 should have been moved to R8:R11 when I updated the file with the true/false criteria and removed some values for rows that I marked "true".

I therefore tried to come up with a solution myself, and think I have found one. Unfortunately the IF statement sometimes produces and array of values that starts with FALSE, which doesn't work well with the AGGREGATE function. I therefore have to multiply with 1 to turn it into a zero, but that creates a problem with #SPILL! errors in cases where the array doesn't start with FALSE. To handle this I found out that I could use the SWITCH function to turn zero into one if the whole row_num expression in the INDEX formula turns out to return zero. Long story short; the formula that started out quite neat, suddenly turned out to be quite large. Perhaps some of you would like to have a look at it and see if it can be improved?

I also looked at Bo_Ry's formula in cell L3, and it doesn't remove Code values which isn't being used by phase numbers that occur in Table 2. Could this be solved?

Here is the formula I wrote in cell L4 (without the SWITCH statement):

``Please Login or Register  to view this content.``
And here it is, including the SWITCH statement:

``Please Login or Register  to view this content.``
Best regards,
Marbleking

14. ## Re: Multiple lookups ordered by timestamp

This will be my last try.

L3
Formula:
`Please Login or Register  to view this content.`

L4
=IFERROR(INDEX(\$F:\$F,MOD(AGGREGATE(15,6,ROW(\$E\$4:\$E\$34)+ROUND((\$D\$4:\$D\$34+\$E\$4:\$E\$34)*10^9,-6)/MMULT((\$I4:\$K4=\$A\$4:\$A\$34)*(\$B\$4:\$B\$34=L\$3),{1;1;1})/(\$C\$4:\$C\$34="false"),L\$2),1000)),"")

15. ## Re: Multiple lookups ordered by timestamp

Much appreciated, Bo_ry! And very elegant.

16. ## Re: Multiple lookups ordered by timestamp

EDIT: Please refer to the latest post for updated information.

17. ## Re: Multiple lookups ordered by timestamp

Hi,

I have reworked Bo_Ry's formula to work out a solution for this problem that requires the use of a lot of "helper" tables. The solution will not be practically feasible for bigger data sets and I was wondering if someone could have a look and see if it was possible to create formulas that don't require the use of the very cumbersome helper tables.

I am looking to keep Table 8 and Table 10 with mutually independent formulas, in addition to the raw data in Table 1.

Regards,
Marbleking

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1