SOLVED

# INDEX MATCH plus MINIFS

Copper Contributor

# INDEX MATCH plus MINIFS

Hi,

I have a table with data. That contains temperatures and dates, but it's not every day. For example:

DATA

 1 A B C D 2 Date Station A Temp Station B Temp Station C Temp 3 Nov 1 1 3 5 4 Nov 2 2 4 6 5 Nov 3 7 5 3 6 Nov 5 8 6 4 7 Nov 6 7 5 9 8 Nov 9 10 6 8

Main Goal: I want to create a formula to pull the minimum temperature between the three stations for a specific date if there is data available (e.g. what's the lowest temperature on Nov 1).

Secondary Goal (Optional): Pull the station associated with the min value.

My set up to populate the table:

RESULT

 1 A B C 2 Date Min Temp Station 3 Nov 1 4 Nov 2 5 Nov 3 6 Nov 4 7 Nov 5 8 Nov 6 9 Nov 7 10 Nov 8 11 Nov 9

When I write my formula, I assume I believe I need an INDEX/MATCH to see if the raw DATA has data for that date. For example, Nov 1 has DATA, so my formula should detect that 1 is the minimum value in the row, and my RESULT in B3 should return 1. There is no DATA for Nov 4, so my RESULT in B6 would just be #N/A.

If anyone could provide any ideas, that would be greatly appreciated!

Extra Information

I have done some research and I found two sites that give two different formulas (for my main goal, I haven't looked at my second goal yet).

Option A - It works, but I don't understand a portion of the formula

https://www.exceldemy.com/index-match-minimum-value/

• Under section 4, "Fusing SMALL Function with Index-Match"
• =SMALL(INDEX(\$C\$4:\$G\$14, MATCH(\$E\$16, \$B\$4:\$B\$14, 0), 0), ROWS(\$D\$17:\$D17))

My formula is:

=SMALL(INDEX(\$B\$2:\$D\$7,MATCH(A12,\$A\$2:\$A\$7,0),0),ROWS(A12:A12))

• I understand the formula up until ROWS. I have no clue what it's doing there, and the formula doesn't work without it, so hopefully someone can explain it to me. Thanks!
• I also like how this is dynamic. I can click and drag and the other cells below will populate correctly

Option B - Couldn't get it to work

• =MINIFS(AF1:AL1,INDEX(AF2:AL15,MATCH(AQ3,D2:D15,0),0),0 --> Feb 19, 2020 comment

Option C - Seems straightforward, but seems to have some faults

• =INDEX(E:E,MATCH(MINIFS(B:B,C:C,J2),B:B,0))

My formula is:

=INDEX(B3:I3,MATCH(MINIFS(A2:A4,A2:A4,A11),A2:A4,0))

• This is sample data, but in my actual spreadsheet, it doesn't necessarily return the minimum value
• I can't click and drag to populate other cells because missing dates causes mis-alignment, so this seems better suited when data is less dynamic or cleaner (for a lack of better words)
• Not understanding certain sections of the formula when I try to adapt it to my needs
• INDEX(B3:I3,MATCH(MINIFS(X?:X?,A2:A4,A11),Y?:Y?,0))
• I understand the structure of MINIFS, but maybe because it's combined with matching that throws me off. In the spot of X?:X? and Y?:Y?, I originally had B3:I3 in both places, but it didn't work until I changed both to A2:A4.

2 Replies
best response confirmed by RecycleBin_Rob (Copper Contributor)
Solution

# Re: INDEX MATCH plus MINIFS

=MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),))
=INDEX(\$C\$3:\$E\$3,MATCH(MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),)),INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),),0))

An alternative could be these formulas for Min Temp and Station. If you want to get rid of the #NA results you can wrap the formula in IFERROR.

=IFERROR(MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),)),"")
=IFERROR(INDEX(\$C\$3:\$E\$3,MATCH(MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),)),INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),),0)),"")

# Re: INDEX MATCH plus MINIFS

Occam's razor - simplest solution is the best. Not sure how I convinced myself I needed MINIFS when MIN works just fine. Thanks!

1 best response

Accepted Solutions
best response confirmed by RecycleBin_Rob (Copper Contributor)
Solution

# Re: INDEX MATCH plus MINIFS

=MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),))
=INDEX(\$C\$3:\$E\$3,MATCH(MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),)),INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),),0))

An alternative could be these formulas for Min Temp and Station. If you want to get rid of the #NA results you can wrap the formula in IFERROR.

=IFERROR(MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),)),"")
=IFERROR(INDEX(\$C\$3:\$E\$3,MATCH(MIN(INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),)),INDEX(\$C\$4:\$E\$9,MATCH(H4,\$B\$4:\$B\$9,0),),0)),"")