Search code examples
excelexcel-formuladatatablearray-formulas

Need help to find values in arrays

i need help to find the "Temperature min record (°C)" between the 3 months in the cell G3 based on the name of the city in F3. Any ideas?

The image below show my sheet

enter image description here

Thank you for any kind of help!

Solution

  • Perhaps you could try using functions like INDEX( ) instead of using volatile functions like OFFSET( ) & INDIRECT( ) .

    A Friendly Suggestion

    OFFSET( ) is a flexible function and it can point to a different-sized range that is calculated on-the-fly. But it is a volatile function. If you go to a completely unrelated cell and enter a number, all of the OFFSET( ) functions will calculate—even if that cell has nothing to do with.

    It has been found, Excel is very careful to spend time calculating the cells that needs to be calculated. But once you use the OFFSET( ), all of the OFFSET( ) cells, plus everything downline from the OFFSET(), starts calculating after every change in the worksheet.

    Why it matters ? INDEX( ) is not volatile. You enjoy all of the flexible goodness of OFFSET( ) without the time-sucking recalculations over and over.

    Here is a screenshot showing the formula used:

    enter image description here

    • Formula used in cell G3

    =MIN(INDEX(B:D,MATCH(F3,A:A,0)+6,))

    The above formula refers the City and moves 6 rows down to get the desired values.

    • But if you want to make it more dynamic, i.e. if you want to change the Temperature labels then. (Caveat - Change the function MIN( ) or MAX( ) as per requirements)

    =MIN(INDEX(B:D,MATCH(F3,A:A,0)+MATCH(G2,A:A,0)-1,))

    A .Gif

    enter image description here

    One more alternative approach, i think this is proper:

    =LET(
     a,INDEX(B:D,MATCH(F3,A:A,0)+MATCH($G$2,A:A,0)-1,),
     SWITCH(TEXTBEFORE(TEXTAFTER($G$2," ")," "),"max",MAX(a),"min",MIN(a),a))

    The above formula also works as per your requirement, if you change the temperature labels it will work, but note that, since I am not sure what you want to do with the one written as --Temperature ave. (°C)-- for this the output will spill right, if you have any specific requirement for that temperature, just wrap the desired function like we did for MIN( ) and MAX( ).

    enter image description here