CODEWITHSUNDEEP
excelexcel-formula
Melvin Richard
Melvin Richard

Reputation: 423

Getting the minimum number from the previous row

There are four rows in my table

1) Should get the cell having the maximum number in the fourth row and get the corresponding cell in the 3rd row

2) So with that minimum value we should be able to take the corresponding cell values in the second and the first row

Example

1) in the table there are two values with the maximum number i.e 100. But the corresponding cell with the minimum value is 32, 2) from 32 we should able to fetch bbb and 2. Is there a way to do that in excel formula

(1) In the table there are two values with the maximum number i.e 100. But the corresponding cell with the minimum value is '32' from row 3.
(2) From 32 we should able to fetch 'bbb' from row 2 and '2' from row 1. Is there a way to do that in excel formula

I tried it with the below formula but it dint work the way I needed it to work

=INDEX((B3:G3),MATCH(MAX(B4:G4),B4:G4,MIN(B3:F3)))

Upvotes: 0

Views: 98

Answers (1)

newacc2240
newacc2240

Reputation: 1425

=OFFSET($A$1,ROW(A1)-1,MATCH(LARGE($A$4:$D$4*10000-$A$3:$D$3,1),$A$4:$D$4*10000-$A$3:$D$3,0)-1)

Array formula, press ctrl + shift + enter to complete.

Note: If the value of the third row is larger than 10000, you should update the formula from 10000 to 1000000 or something more than that.

Data arrange like this

Upvotes: 1

Related Questions