Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: INDEX MATCH MAX IF Function without the use of an array

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default INDEX MATCH MAX IF Function without the use of an array

    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.

  2. #2
    New Member
    Join Date
    Nov 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    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.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    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.

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    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.

    Sheet1

    ABC
    1 12.03.2018 15
    212.03.2018 15 303030
    312.03.2018 65 240
    413.03.2018 15 270
    512.03.2018 25 2800
    612.03.2018 15 66
    712.03.2018 15 2929

    Spreadsheet Formulas
    CellFormula
    B2=IF(TRIM(LEFT(A2,14))=$B$1,RIGHT(A2:A7,2),0)+0
    C2=MAX(B2:B7)


    Excel tables to the web >> Excel Jeanie HTML 4

  6. #6
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    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:

    9. Mrz. 18 T5T3 14 13,32 6,00 7,3 1,5
    10. Mrz. 18 T5T3 13 13,33 6,00 6,4 1
    10. Mrz. 18 T5T3 9 13,24 6,00 7,5 9
    10. Mrz. 18 T5T3 11 13,29 6,50 7,4 2
    11. Mrz. 18 T5T3 7 13,24 6,50 7,7 0
    11. Mrz. 18 T5T3 5 13,35 6,00 6,5 5
    11. Mrz. 18 T5T3 3 13,27 6,00 6,4 34
    12. Mrz. 18 T5T3 13 13,39 6,00 6,4 32,5
    13. Mrz. 18 T5T3 0 70 5 13,35(formula needed)

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    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.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    Quote Originally Posted by FredericA View Post
    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?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    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.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default Re: INDEX MATCH MAX IF Function without the use of an array

    Would you please specify how you would get get 13,35 manually for the sample you posted?
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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