Forum Discussion
Help With A Formula
Hi Jordan Miller,
Enter the following array formula in cell P2.
=INDEX(P$4:P$2000,-3+MAX(IF($O$4:$O$2000="x",ROW($O$4:$O$2000),0)),1)
The result will be the cell from column P, corresponding to the last entry of x in column O.
If you use the below Array Formula, you will be able to get the result from column P, corresonding to the last, second last or any other entry of x in column O.
=IFERROR(INDEX(P$4:P$24,-3+LARGE(IF($O$4:$O$24="x",ROW($O$4:$O$24),0),2),1),"-")
Change the last 2 to 3, if you want the third last entry, instead of the second last.
The formulas will work even if the entries of x are not in each cell of column O.
Change the cell references as required.
Array Formulas are entered using Control + Shift + Enter, instead of Enter.
Do let me know if this is what you wanted to do or if any changes are required.
Thank you very much for your help. However, when I copy and paste the formula I get #VALUE! and can't figure out why.
Hi Jordan Miller,
One possibility could be that you have entered the Array Formula using Enter, instead of Control + Shift + Enter.
Other possibilities could include incorrect reference range or incorrect data.
Try entering the Formula with Control + Shift + Enter or else attach a sample file, with test data in place of the actual data.
I have attached the sample file for your reference. Formulas are in P2 and S2.
Do let me know once the problem is solved or you still face some difficulties.
Vijaykumar Shetye,
Panaji, Goa, India
Thank you so much! One last question. Looking at the picture I attached, is there a formula to make L:2 say the last "milestone" that has an "x" by it? Basically, I want L:2 to say "One Year" until I reach the next milestone and once I have reached that milestone it will automatically say Two Years. It currently doesn't do this because I have spaces in between that the formula is reading and saying "0".
