I have this row, and want to know the value of the first row (dates) of the last non-empty cell. In this example, it should return 1/11/2018. Given the input of "Stock A", how do I achieve this using Excel formulas?
Asked
Active
Viewed 1,996 times
1
-
1Take a look at [this explanation](https://exceljet.net/formula/last-column-number-in-range) – PeterT Jun 07 '18 at 20:09
-
@JohnColeman 1/11/2018 is indeed the date which corresponds to the last non-empty cell in the row and that's exactly the output I want. 1/12/2018 is just given, and I have no control over it. – JungleDiff Jun 07 '18 at 20:17
-
@JohnColeman sorry for the confusion. Yes the output should be 1/11/2018. What can I do? – JungleDiff Jun 07 '18 at 20:21
-
@JohnColeman what are you talking about? – JungleDiff Jun 07 '18 at 20:24
-
@JohnColeman When I said "What can I do", I was asking "Now that I fixed the error, how should I solve this original problem". – JungleDiff Jun 07 '18 at 20:29
-
Simple misunderstanding then. In any event -- the link that @PeterT gave basically solves the problem (perhaps combined with `hlookup()`) – John Coleman Jun 07 '18 at 20:30
-
For your lookup, will the last populated cell in a row always be the maximum value in that row as shown in your example? – tigeravatar Jun 07 '18 at 20:38
-
@tigeravatar No it won't necessarily be the maximum value. That was just an example! – JungleDiff Jun 07 '18 at 20:38
2 Answers
0
Assuming the data is in Sheet1, and your lookup stock code ("Stock A" in this case) is on Sheet2 cell A2, use this formula in sheet2, cell B2 to get the desired date (adjust range references to suit your actual data):
=LOOKUP(2,1/(INDEX(Sheet1!$B:$M,MATCH(A2,Sheet1!$A:$A,0),0)<>""),Sheet1!$B$1:$M$1)
Note that you'll probably need to format the formula cell as a Date so it displays the date properly instead of the Excel Datecode.
tigeravatar
- 26,199
- 5
- 30
- 38