INDEX MATCH MAX IF Function without the use of an array

[FredericA]

Can someone please help me with the following case:

A spreadsheet containing dates and values (multiple times same date is occuring):

A(date) B (Silo) C (lab value)
12.03.2018 15 30
12.03.2018 9 24
13.03.2018 9 27

If I fill in a new row with some numbers, it has to give me the most recent lab value from that silo, e.g.:
15.03.2018 15 has to return 30

I managed this using the following array function (CFE)
{=IFERROR(INDEX($C$10:$C173;MATCH(MAX(IF($B$10:$B173=$B174;$A$10:$A173));IF($B$10:$B173=$B174;$A$10:$A173);0));0)}

But I am struggeling to find the good form to execute this formula NOT as an array, I´d like to have it not as an array because the workbook will be shared and in that case each time you want to change or overwrite the formula you have to unshare.

Help would be really appreciated.

 

[FredericA]

Ah ok sorry for not being clear, at the last row in my example I filled in 5 manually (the number of my Silo), so the formule is looking back in the data where he finds this 5 for the most recent date. In this example it is 3 rows above him at the 11th of March that the lab value in Silo 5 was 13,35, so this is the result of my formula
Even when the table is bigger and for example the 6th of March the lab value in Silo 5 was 12,9 it would not show this value as it looked for the most recent one.

 

[Aladin Akyurek]

9. Mrz. 18

is apparently 2018-03-09, right?

What do 0 and 70 mean which you show on row 11?

 

[FredericA]

Yes indeed it's a date but excel just looks to it as a number I guess, so same dates are same numbers.
Don't bother about the 0 and 70, I was just adding these columns to form a calculation between with the value I am trying to get via that formula multiplied with the tons of product still in the silo and the new product with new lab value. But it does not affect my question posted here.

 

[Aladin Akyurek]

9. Mrz. 18

is apparently 2018-03-09, right?

What do 0 and 70 mean which you show on row 11?

Yes indeed it's a date but excel just looks to it as a number I guess, so same dates are same numbers.
Don't bother about the 0 and 70, I was just adding these columns to form a calculation between with the value I am trying to get via that formula multiplied with the tons of product still in the silo and the new product with new lab value. But it does not affect my question posted here.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

2​

[/td][td]

2018-03-09​

[/td][td]T5T3[/td][td][/td][td][/td][td]

14​

[/td][td]

13.32​

[/td][td]

6​

[/td][td]

7.3​

[/td][td]

1.5​

[/td][/tr]
[tr][td]

3​

[/td][td]

2018-03-10​

[/td][td]T5T3[/td][td][/td][td][/td][td]

13​

[/td][td]

13.33​

[/td][td]

6​

[/td][td]

6.4​

[/td][td]

1​

[/td][/tr]
[tr][td]

4​

[/td][td]

2018-03-10​

[/td][td]T5T3[/td][td][/td][td][/td][td]

9​

[/td][td]

13.24​

[/td][td]

6​

[/td][td]

7.5​

[/td][td]

9​

[/td][/tr]
[tr][td]

5​

[/td][td]

2018-03-10​

[/td][td]T5T3[/td][td][/td][td][/td][td]

11​

[/td][td]

13.29​

[/td][td]

6.5​

[/td][td]

7.4​

[/td][td]

2​

[/td][/tr]
[tr][td]

6​

[/td][td]

2018-03-11​

[/td][td]T5T3[/td][td][/td][td][/td][td]

7​

[/td][td]

13.24​

[/td][td]

6.5​

[/td][td]

7.7​

[/td][td]

0​

[/td][/tr]
[tr][td]

7​

[/td][td]

2018-03-11​

[/td][td]T5T3[/td][td][/td][td][/td][td]

5​

[/td][td]

13.35​

[/td][td]

6​

[/td][td]

6.5​

[/td][td]

5​

[/td][/tr]
[tr][td]

8​

[/td][td]

2018-03-11​

[/td][td]T5T3[/td][td][/td][td][/td][td]

3​

[/td][td]

13.27​

[/td][td]

6​

[/td][td]

6.4​

[/td][td]

34​

[/td][/tr]
[tr][td]

9​

[/td][td]

2018-03-12​

[/td][td]T5T3[/td][td][/td][td][/td][td]

13​

[/td][td]

13.39​

[/td][td]

6​

[/td][td]

6.4​

[/td][td]

32.5​

[/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

2018-03-13​

[/td][td]T5T3[/td][td]

0​

[/td][td]

70​

[/td][td]

5​

[/td][td]

13.35​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F11 just enter:

=LOOKUP(9.99999999999999E+307,1/(($E$2:$E$9=E11)*$A$2:$A$9),$F$2:$F$9)

 

[FredericA]

Wow genius, it works! Could you explain how it exactly works?

Thanks a lot in any case for taking the time to help!

 

[Aladin Akyurek]

Wow genius, it works! Could you explain how it exactly works?

Thanks a lot in any case for taking the time to help!

Great to hear.

LOOKUP with that big number (a limit constant of Excel itself is used to pick out the last numerical value the array it's fed with.

Here we have a calculated numerical array, that is:

1/(($E$2:$E$9=E11)*$A$2:$A$9)

which multiplies the evaluation E2:E9 = E11 which yields TRUE or FALSE values.
Multiplying these TRUE/FALSE values pairwise with the numerical date range A2:A9 deliver a bunch of zeros and and non-zeros.
Note that TRUE * 2018-03-20 >> 2018-03-20 and FALSE * 2018-03-20.
Thus we get 0's and dates.

Dividing 1 by the set of 0's and dates gives a bunch of #DIV/0!'s and fractions (numbers).

LOOKUP with that big number picks out the last fraction from the division set and correlates its position within that set with a value in F2:F9 and returns that F value.

Hope this helps.

 

[Rickmaurinus]

You could try using SUMPRODUCT twice.

The formula I put in column C (cel C6) =SUMPRODUCT( (C$2:C5) * (B$2:B5 = B6) * (A$2:A5 = SUMPRODUCT( MAX( (A$2:A5) * (B$2:B5 = B6)))))

Let's start at the end formula: I start out by finding the latest date for the specific Silo. That formula is =SUMPRODUCT( MAX( (A$2:A5) * (B$2:B5 = B6))).
Then I use another SUMPRODUCT to find the right Lab value, for the Silo & the just calculated date. When you put it together you get the result.

You could also do this using a SUMIF formula. Then the formula for cell C6 should be:
=SUMIFS( C$2:C5, B$2:B5, B6, A$2:A5, SUMPRODUCT( MAX( (A$2:A5) * (B$2:B5 = B6)))).


I'm using setup:
[TABLE="width: 205"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]A(date)[/TD]
[TD]B(Silo)[/TD]
[TD]C (lab value)[/TD]
[/TR]
[TR]
[TD="align: right"]12/03/2018[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]12/03/2018[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]13/03/2018[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]13/03/2018[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]15/03/2018[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]30,00

[/TD]
[/TR]
</tbody>[/TABLE]

Hope that works!
Rick

 

[FredericA]

Yes very clear, did not know that LOOKUP big number functionality, nice approach!

 

[FredericA]

Nice alternative, this will work for sure too, thanks! Is there anywhere info on what calculations are using the most CPU and thus making your file more heavy if you have over 1000 cells containng such formulas?

 

[Aladin Akyurek]

LOOKUP formula is faster...