• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help related to LET function

R

razaas

Member
Hi, Below formula is working fine and spills result as desired, I want help to add a headings row in the top row of the spill range, my spill range has 3 columns, I want to have "Value1", "Value2","Value3" as column names. Thanks
=LET(end_of_month_dates1, EOMONTH(--Table3[Date], 0),
end_of_month_dates2, EOMONTH(--Table3[Date], -1),
unique_dates1, UNIQUE(SORT(end_of_month_dates1, , -1)),
unique_dates2, UNIQUE(SORT(end_of_month_dates2, , -1)),
current_month, INDEX(Table3[End of Month], MATCH(unique_dates1, Table3[Date], 0)),
previous_month,INDEX(Table3[End of Month], MATCH(unique_dates2, Table3[Date], 0)),
month_on_month,current_month-previous_month,
IFERROR(CHOOSE({1,2,3}, unique_dates1, current_month,month_on_month),0)
)
 
You can add in VSTACK:

=LET(end_of_month_dates1, EOMONTH(--Table3[Date], 0),
end_of_month_dates2, EOMONTH(--Table3[Date], -1),
unique_dates1, UNIQUE(SORT(end_of_month_dates1, , -1)),
unique_dates2, UNIQUE(SORT(end_of_month_dates2, , -1)),
current_month, INDEX(Table3[End of Month], MATCH(unique_dates1, Table3[Date], 0)),
previous_month,INDEX(Table3[End of Month], MATCH(unique_dates2, Table3[Date], 0)),
month_on_month,current_month-previous_month,
VSTACK({"Value1","Value2","Value2"},IFERROR(CHOOSE({1,2,3}, unique_dates1, current_month,month_on_month),0))
)
 
Debaser said:
You can add in VSTACK:

=LET(end_of_month_dates1, EOMONTH(--Table3[Date], 0),
end_of_month_dates2, EOMONTH(--Table3[Date], -1),
unique_dates1, UNIQUE(SORT(end_of_month_dates1, , -1)),
unique_dates2, UNIQUE(SORT(end_of_month_dates2, , -1)),
current_month, INDEX(Table3[End of Month], MATCH(unique_dates1, Table3[Date], 0)),
previous_month,INDEX(Table3[End of Month], MATCH(unique_dates2, Table3[Date], 0)),
month_on_month,current_month-previous_month,
VSTACK({"Value1","Value2","Value2"},IFERROR(CHOOSE({1,2,3}, unique_dates1, current_month,month_on_month),0))
)
Click to expand...
Thanks a lot for the reply, my version of Excel does not have VSTACK Function, any other option? Thanks
 
Debaser said:
Add the titles manually and put the LET formula in the cell below?
Click to expand...
Thanks for the suggestion, I had already done that but was expecting to find solution to spill thru 1 formula, I will wait for more replies and possibly a solution. Thanks
 
The following variant should be compatible with Excel 2021:

Code: 
=LET(
    end_of_month_dates1, EOMONTH(--Table3[Date], 0),
    end_of_month_dates2, EOMONTH(--Table3[Date], -1),
    unique_dates1,  UNIQUE(SORT(end_of_month_dates1, , -1)),
    unique_dates2,  UNIQUE(SORT(end_of_month_dates2, , -1)),
    current_month,  XLOOKUP(unique_dates1, Table3[Date], Table3[End of Month]),
    previous_month, XLOOKUP(unique_dates2, Table3[Date], Table3[End of Month]),
    month_on_month, current_month - previous_month,
    results,  IFERROR(CHOOSE({1,2,3}, unique_dates1, current_month, month_on_month), 0),
    row_nums, SEQUENCE(ROWS(results) + 1, , 0),
    IF(row_nums, INDEX(results, row_nums, {1,2,3}), {"Value1","Value2","Value3"})
)
 
djc said:
The following variant should be compatible with Excel 2021:

Code: 
=LET(
    end_of_month_dates1, EOMONTH(--Table3[Date], 0),
    end_of_month_dates2, EOMONTH(--Table3[Date], -1),
    unique_dates1,  UNIQUE(SORT(end_of_month_dates1, , -1)),
    unique_dates2,  UNIQUE(SORT(end_of_month_dates2, , -1)),
    current_month,  XLOOKUP(unique_dates1, Table3[Date], Table3[End of Month]),
    previous_month, XLOOKUP(unique_dates2, Table3[Date], Table3[End of Month]),
    month_on_month, current_month - previous_month,
    results,  IFERROR(CHOOSE({1,2,3}, unique_dates1, current_month, month_on_month), 0),
    row_nums, SEQUENCE(ROWS(results) + 1, , 0),
    IF(row_nums, INDEX(results, row_nums, {1,2,3}), {"Value1","Value2","Value3"})
)
Click to expand...
Thanks a lot, it works.
 
You must log in or register to reply here.
Back
Top