Thread: lookup formula

You don't really need a lookup, because you are not searching for a value. You can probably do this with some combination of SUMIF, COUNT, INDEX. But we need to know a little more about how your data is organized. Does your workbook deal with multiple teams? What data, and what columns is it in?

I am not sure your title matches your question; you might consider changing it to something like "Sum five most recent events".

Basically something like: =SUMIFS(C1:C20;A1:A20;">=10.3.2010";B1:B20;"a")

But this has several problems: you need to enter 5th dat manualy (of course, you can use some aditional cells like D1 for date and D2 for club)

=SUMIFS(C1:C20;A1:A20;">="&D1;B1:B20;D2)

But best way would be that you upload some example workbook with some example data

see this virtually identical thread from a few days ago: http://www.excelforum.com/excel-prog...lculation.html

(there is an attachment in one of the last posts showing a formulae based approach)

The other thread was based on results in the reverse order to yours - ie most recent listed first.

For the opposite - using your file:

Code:

G2: =IF(ISNUMBER($L2);SUMIF($B3:INDEX($C:$C;$L2);$B2;$D3:INDEX($E:$E,$L2));"")
copied down

H2:
=IF(ISERROR($L2);"";SUMPRODUCT(($B3:$B$500=$B2)+($C3:$C$500=$B2);($D3:$D$500*($B3:$B$500<>$B2))+($E3:$E$500*($C3:$C$500<>$B2));--(ROW($B3:$B$500)<=$L2)))
copied down

I2:
=IF(ISNUMBER($L2);SUMIF($B3:INDEX($C:$C;$M2);$C2;$D3:INDEX($E:$E;$M2));"")
copied down

J2:
=IF(ISERROR($M2);"";SUMPRODUCT(($B3:$B$500=$C2)+($C3:$C$500=$C2);($D3:$D$500*($B3:$B$500<>$C2))+($E3:$E$500*($C3:$C$500<>$C2));--(ROW($B3:$B$500)<=$M2)))
copied down

L2:
=SMALL(IF(ISNUMBER(SEARCH("@"&$B2&"@";$N3:$N$500));ROW($B3:$B$500));MIN(6;COUNTIF($B3:$C$500;$B2)))
confirmed with CTRL + SHIFT + ENTER
copied down

M2:
=SMALL(IF(ISNUMBER(SEARCH("@"&$C2&"@";$N3:$N$500));ROW($B3:$B$500));MIN(6;COUNTIF($B3:$C$500;C2)))
confirmed with CTRL + SHIFT + ENTER
copied down

Note L & M are both Arrays... as per instructions you must ensure you commit using CTRL + SHIFT + ENTER as opposed to Enter as per "normal" formulae.

I would suggest you modify the formulae in L & M such that less than 6 results generates an error .... so replace:

MIN(6;COUNTIF($B3:$C$500;$B2))

with

6

in both L & M formulae - remember to reset the Arrays with CTRL + SHIFT + ENTER once modified.


Note that there is an error in the previously suggested formula will need to be corrected, I2 should read:

ISNUMBER($M2)

and not L2 as is presently the case.