Assume "D" column of 5 cells each containing a number (ie 18, 25, 7, 11,32). Two rows below and in say D7 I want this formula =MIN(D1:D5). I want in C7 a formula to return the contents of the first cell in the row that contains the MIN value that shows in D7. However the MIN value will change when different numbers are put in the cells in column D1:D5 because the cell in D7 changes to show the new MIN. I was trying a formula using INDEX - I know this next one is incorrect but it was a start!! =INDEX(MIN(F7:F11),ROW()) So I need the contents of first cell from the row that happens to be the min value of col. range D1:D5 which varies with the inputted numbers in that column.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
excel 2010 return 1st cell in row based on varible cell reference
- Thread starter canar1
- Start date
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Hard to follow... Is it possible for you to post a tiny (yes, tiny) sample along with the outcome you want to see?
Upvote
0
oh oh -- shows what cut and past does! It should be D7:D11 not F...
However the formula with the D range didn't work.
However the formula with the D range didn't work.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Still no sample, a tiny one?
Upvote
0
Ok, Sorry everyone for the confusion. I have two different spreadsheets that I have working with and the cell references in my descripton ARE all messed up. Here's another try ....
--A--- B---C--- D--- E
7 1----------------- 18
8 2----------------- 25
9 3------------------7
10 4---------------- 11
11 5---------------- 32
12
13 ------------------ =MIN(E7:E11)
I tried just using spaces to show the numbers in E7 to E11 but they disappeared when I previewed the post so I used dashes. In cell E13 is the MIN formula which naturally, in this case, returns 7 from E9 cell since it is the lowest of the E7:E11 range. The numbers in the E7:E11 range are inputted once a week and so change every week.
In cell D13 I want excel to put the contents of, in this case, the first cell of the row that belongs to the MIN value, which is 3 (which happens to be student# 3).
The value displayed in cell D13 will vary depending on what value is the lowest in the E7:E11
I hope this clears up the confusion. Thanks for bearing with me on this.
--A--- B---C--- D--- E
7 1----------------- 18
8 2----------------- 25
9 3------------------7
10 4---------------- 11
11 5---------------- 32
12
13 ------------------ =MIN(E7:E11)
I tried just using spaces to show the numbers in E7 to E11 but they disappeared when I previewed the post so I used dashes. In cell E13 is the MIN formula which naturally, in this case, returns 7 from E9 cell since it is the lowest of the E7:E11 range. The numbers in the E7:E11 range are inputted once a week and so change every week.
In cell D13 I want excel to put the contents of, in this case, the first cell of the row that belongs to the MIN value, which is 3 (which happens to be student# 3).
The value displayed in cell D13 will vary depending on what value is the lowest in the E7:E11
I hope this clears up the confusion. Thanks for bearing with me on this.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Try...
D13:
=INDEX(A7:A11,MATCH(E13,E7:E11,0))
D13:
=INDEX(A7:A11,MATCH(E13,E7:E11,0))
Upvote
0
Works like a charm !!!
I've been trying to get this for some time. Now I have to figure out HOW it works.
Thanks again.
I've been trying to get this for some time. Now I have to figure out HOW it works.
Thanks again.
Upvote
0
Do I understand correctly for the formula in D13 (which as I said works)
=INDEX(A7:A11,MATCH(E13:,E7:E11,0))
The MATCH function looks to cell E13 and find a value. It then looks in the cells between (in the range of) E7 to E11 and sees the same number in a particular row. The INDEX function then looks in the rows of A7:A11 and ....... here's where I don't how it knows it should return the cell in that particular row. What would happen if the numbers where in B7:B11. I'm thinking I don't understand what teh INDEX function does. BUT I do appreciate everyone's input.
=INDEX(A7:A11,MATCH(E13:,E7:E11,0))
The MATCH function looks to cell E13 and find a value. It then looks in the cells between (in the range of) E7 to E11 and sees the same number in a particular row. The INDEX function then looks in the rows of A7:A11 and ....... here's where I don't how it knows it should return the cell in that particular row. What would happen if the numbers where in B7:B11. I'm thinking I don't understand what teh INDEX function does. BUT I do appreciate everyone's input.
Upvote
0
ITWare2008
Board Regular
- Joined
- Apr 16, 2010
- Messages
- 174
- Office Version
- 365
- 2016
- Platform
- Windows
Hello Canar1,
Here it goes the formula explanation.
This kind of formula are known as nested formula, because it has one or more formulas inside another one.
The first element to be evaluated it is the MATCH function.
The Match function will look for the value in cell E13 in the range E7:E11 and returns a value which means the relative position of the E13 value within the range.
That value will be passed to the INDEX function, which will return the contents of the list in column A which has the same relative position of the value returned by the MATCH function.
For instance. If MATCH function returns 3 INDEX function will return the content of the third item of the list you defined of in column A. (the third position in A7:A11 it's A9)
Did it help you?
Vândalo
Here it goes the formula explanation.
This kind of formula are known as nested formula, because it has one or more formulas inside another one.
The first element to be evaluated it is the MATCH function.
The Match function will look for the value in cell E13 in the range E7:E11 and returns a value which means the relative position of the E13 value within the range.
That value will be passed to the INDEX function, which will return the contents of the list in column A which has the same relative position of the value returned by the MATCH function.
For instance. If MATCH function returns 3 INDEX function will return the content of the third item of the list you defined of in column A. (the third position in A7:A11 it's A9)
Did it help you?
Vândalo
Upvote
0
Similar threads
- Solved
- Solved
