Finding the MIN in a row, and then also the cell 2 spots to the LEFT of the MIN cell.

Copper Contributor

I'm trying to make my formula work to find the lowest price in a ROW of data.  I've sorted out to use the MIN function - no problem.  The issue now is that I need to also figure a formula to give me the data in the cell two spots to the left of that MIN lowest price. I've tried the following formula: =INDEX(A1:Z1, MATCH(MIN(A1:Z1), A1:Z1, 0)-1) and only get a #N/A result.  I've included some "dummy data" as an example. Ideally, in cell B2 I would end up with "Market Basket" - the lowest price in the B row is $0.30 and two cells to the left shows the store Market Basket. 



2 Replies
best response confirmed by VI_Migration (Silver Contributor)


=INDEX(C2:K2, MATCH(MIN(C2:K2), C2:K2, 0)-2)

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.


@OliverScheurich This works!! Looks like I was using the wrong range and also using -1 instead of -2.  Thank you so very much!!