A complicated reverse lookup formula

[mclements93]

So I have monthly cashflows for 7 years. I have it so that month's cash flows adds on to the previous month's. I need a formula (or two) that can return the column and row labels for when the first non-zero number appears.

2014 2015 2016 2017 2018 2019 2020
Jan $(100) $(1,300) $(2,390) $(2,260) $(1,940) $(740) $460
Feb $(200) $(1,400) $(2,380) $(2,240) $(1,840) $(640) $560
Mar $(300) $(1,500) $(2,370) $(2,220) $(1,740) $(540) $660
Apr $(400) $(1,600) $(2,360) $(2,200) $(1,640) $(440) $760
May $(500) $(1,700) $(2,350) $(2,180) $(1,540) $(340) $860
Jun $(600) $(1,800) $(2,340) $(2,160) $(1,440) $(240) $960
Jul $(700) $(1,900) $(2,330) $(2,140) $(1,340) $(140) $1,060
Aug $(800) $(2,000) $(2,320) $(2,120) $(1,240) $(40) $1,160
Sep $(900) $(2,100) $(2,310) $(2,100) $(1,140) $60 $1,260
Oct $(1,000) $(2,200) $(2,300) $(2,080) $(1,040) $160 $1,360
Nov $(1,100) $(2,300) $(2,290) $(2,060) $(940) $260 $1,460
Dec $(1,200) $(2,400) $(2,280) $(2,040) $(840) $360 $1,560

So in this example, I would need a formula that could find the first number above $0 (in this case it is $60) and return the month and year (Sep 2019)

Any help would be amazing!

[mikeTRON]

First off, I reccomend attaching a sample workbook instead of pasting to the forum like above. Second post a desired end state in the example so we can understand what you are trying to accomplish. You are much more likely to get specific help .

I would try but I need a few examples to understand what answer youa re trying to get at. I can find the first non negative datapoint in each row, but I am nbot sure if that is what you are trying to get at.

[mclements93]

Sorry, didn't see that you could post an attachment!

I'm able to find the minimum value above zero by using the array function in the attached workbook. I want to know what month and year represents the first number above zero. The difficulty is that it could be in any row or column (different products have numbers).

[Miraun]

Would you be open to using user defined functions to achieve this goal?

It would require a file extension change from XLSX to XLSM... Dunno the requirements, so I'd rather ask before assuming that you're fine with VBA.

[GeneralDisarray]

For month use this array formula (based on your value cell E17): =INDIRECT("A" &MIN(IF(B3:H14=E17,ROW(B3:H14),"")))

For the year, try this array formula: =INDIRECT("R1" & "C" & MIN(IF(B3:H14=E17,COLUMN(B3:H14),"")),FALSE)

I'm not sure if you need to have it in one cell, but those should be the pieces needed to get you started. I realize you know, but other readers might not, remember to press cntrl+shift+enter after writing the formulas to get the "array" magic to happen.

[Miraun]

Please Login or Register  to view this content.

Where you use Miraun(A1:H14)
However, with the example you gave, there's a little complexity with the hidden row2... don't know if that will be consistent or not, because otherwise the first entry under 2016 gets hit.

[GeneralDisarray]

I see, you need it in one cell. You don't need VBA for this.

REMEMBER, confirm this with "cntrl+shift+enter"

=INDIRECT("A" &MIN(IF(B3:H14=MIN(IF(B3:H14>0,B3:H14)),ROW(B3:H14),""))) & " - " &INDIRECT("R1" & "C" & MIN(IF(B3:H14=MIN(IF(B3:H14>0,B3:H14)),COLUMN(B3:H14),"")),FALSE) & ": $" &MIN(IF(B3:H14>0,B3:H14))

That will give you this: Sep - 2019: $60. I thought you might like the month, a little dash, then the value.

[Miraun]

Hi General;

Your equation is amazing, however, there are two concerns with the result:
1) It's identifying the lowest positive value, not necessarily the first occurrence of positive.
2) If two identical values are identified, it will utilize the minimum of Year, and minimum of month. Therefore if you have January 2018 and December 2014 with values of $10, it'll return January 2014. (this was the main factor that I felt I could not overcome, and why I deferred to a UDF)

[GeneralDisarray]

Your UDF is perfectly acceptable - We posted concurrent answers to the same question and I was just trying to play out the solution for educational purposes.

Miraun, the duplicated values do lead to problems. But for the application shown here, a duplicate will not occur.



OP - you can greatly simplify this by abandoning the "matrix" style for the data entry, and use a simpler 3 column table (year,month,value) with a related Pivot table for presentation (see attached).

See the attached for a simple example of what I mean. By placing the dollar values in one column you totally eliminate the problem pointed out by Miraun.

[mclements93]

Wow! You are all truly amazing! Thanks for all of the input. I figured it out and would definitely not have been able to without all of your help. Thank you!