I am building a Google Sheet with tabs for 9 grocery stores. The price per ounce for items at those stores pulls into a dashboard. My formula searches across each row, finds the smallest number, looks up to the header to see which store it is, and returns that value to show where the item can be purchased the cheapest.
The formula works for 65 out of 83 lines. I know why 4 of them don't work -- there isn't actually a value to be returned yet. However, the formula is literally dragged down the page. There is no difference between the ones that are working and the ones that aren't. I can see from the error message that the formula is finding the MIN in the row correctly, so it's stalling out before pulling the header value.
I've tried reformatting every sheet to make sure there are no conflicting text/number formats. I've tried replacing the values with hard numbers instead of formulas. I've tried deleting individual cells to see if there is a rogue in the batch.
=index($E$1:$M$1,match(min($E2:$M2),$E2:$M2))
https://docs.google.com/spreadsheets/d/1D6K0t49KDmH225JAjxiBE8Zd1PNDi3pOtkaB5mgDCK8/edit?usp=sharing
The following formula, when drag-filled down the column, should provide the behavior you desire.
=INDEX($E$1:$M$1,MATCH(MIN($E2:$M2),$E2:$M2,0))
The third argument in the =MATCH formula determines the type of search it performs. The default, search type 1, relies on the data in the range being sorted in ascending order. The data is not sorted, so you should use search type 0. The reason it was working on some of your other rows was just luck—depending on how the data is arranged, the formula will sometimes work on unsorted data, but not reliably.
Functions Used: