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.

 

[OGCV1]

Try copy this formula down
=LOOKUP(2,1/(B$1:B1=B2),C$1:C1)
Or this,
=IFERROR(LOOKUP(2,1/(B$1:B7=B8),C$1:C7),"")
to account for the first entry of each silo.

 

[Aladin Akyurek]

If the ranges change frequently, the user must edit the formula and not forget applying control+shift+enter after any edit. You can define dynamic named ranges so that no edit is necessary:

{=IFERROR(INDEX(LabValue;MATCH(MAX(IF(Silo=$B174;Date));IF(Silo=$B174;Date);0));0)}

If interested, post back.

 

[FredericA]

Hi guys,

Thanks for the answers, unfortunately it did not solve my problem.

OGCV1, it is a list of more then 100 dates and values and several columns, I need to have the corresponding Labvalue of the Silo of my choosing from the most recent date in the file, with some dates reoccuring several times, so what you proposed is not working for my case.

Aladin, very interesting, I did not know that and tried it out on my file, and a smaller new excel file and it did not seem to work. Nevertheless this will not solve my problem. The reason is that I do not want to use arrays, the file will be a shared workbook and that gives and error when you try to change (or give a manual entry) in a cell that is formatted as array. So therefore I was looking for a non-array variation of the function I had proposed.

I have seen threads where they have managed it by using an extra index function, but due to my extra IF MAX criteria to solve the recent date problem, I do not get it working. If someone has other ideas, would be really helpful.

 

[István Hirsch]

Try this. Enter the date and silo into cell B1, then the formula in B2 and copy down. You get the maximum for that date and silo in C2.

Excel Workbook
	A	B	C
1		12.03.2018 15
2	12.03.2018 15 30	30	30
3	12.03.2018 65 24	0
4	13.03.2018 15 27	0
5	12.03.2018 25 280	0
6	12.03.2018 15 6	6
7	12.03.2018 15 29	29
Sheet1

 

[FredericA]

Istvan, nice creative approach but unfortunately it will not help in my case as the layout is important and used with filters for other purposes. So I would really need the above mentioned function the be rewritten not as array (if this is possible and not too complicated). My file rows look like the following:

[TABLE="width: 805"]
<tbody>[TR]
[TD]9. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]14[/TD]
[TD]13,32[/TD]
[TD]6,00[/TD]
[TD]7,3[/TD]
[TD]1,5[/TD]
[/TR]
[TR]
[TD]10. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]13[/TD]
[TD]13,33[/TD]
[TD]6,00[/TD]
[TD]6,4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9[/TD]
[TD]13,24[/TD]
[TD]6,00[/TD]
[TD]7,5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]10. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11[/TD]
[TD]13,29[/TD]
[TD]6,50[/TD]
[TD]7,4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD]13,24[/TD]
[TD]6,50[/TD]
[TD]7,7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD]13,35[/TD]
[TD]6,00[/TD]
[TD]6,5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]11. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]13,27[/TD]
[TD]6,00[/TD]
[TD]6,4[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]12. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]13[/TD]
[TD]13,39[/TD]
[TD]6,00[/TD]
[TD]6,4[/TD]
[TD]32,5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13. Mrz. 18[/TD]
[TD]T5T3[/TD]
[TD]0[/TD]
[TD]70[/TD]
[TD]5[/TD]
[TD]13,35(formula needed)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col><col><col><col><col></colgroup>[/TABLE]

 

[FredericA]

Import to mention maybe is that this formula will be combined with another calculation on top, that is way the number will change each time.

 

[Aladin Akyurek]

Import to mention maybe is that this formula will be combined with another calculation on top, that is way the number will change each time.

How do you get 13,35 for the sample you posted?

 

[FredericA]

With my array function, which works perfectly but which I want to avoid to use as it will become a shared workbook that needs flexibility to change a cell manual (which is not possible when it is an array function in a shared workbook)

{=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)}
I composed this formula based on different existing threats on this forum, I understand it as follows:
-> I need to find a previous lab value so first criteria of my INDEX is column C and it has to look at all previous values (table starts at row 10 and I am entering in row 174)
-> In my MATCH I am using a MAX IF, finding the most recent date that has the SILO number I am looking for
-> I have to add an additional IF function to display for that date the value corresponding to my manual input of SILO number, if I do not add this second IF, it gives me the most recent lab value for the date found in the first IF, without looking anymore if the SILO number is still correct.

 

[Aladin Akyurek]

Would you please specify how you would get get 13,35 manually for the sample you posted?