[HELP] Make a more complex VLOOKUP formula

Pictures are confusing

Attach sample workbook (s) . Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I Attached one excel file called Example which hold all 3 sheets as explained in the second reply
Thank you for heads up

This just sums the whole Q column:
=SUMPRODUCT(Book1!$Q$3:$Q$10;--(TEXT(Book1!$R$3:$R$10;"mmyy")=TEXT(P$25;"mmyy")))

This is what I get, it seems like your excel is a bit different. I cant put in the formula with commas I think i have to use semicolon between stuff. Otherwise I get a warning sign :/

newnew.PNG

Wtf this is so strange. When I copy paste the formula to exact same document I have in the computer it shows as the picture above. Did you add anything else that it isn't showing?

I have no idea what I'm doing wrong, now your file is showing 30 400 602 on some cells :/

It is automatic. I think the function TEXT(....;"mmyy") is generating the wrong value. When I write it i just get 00yy. Like TEXT(P$25;"mmyy"), it just generates 00yy. How do I fix this?

It is working with your excel file but when I copy paste in my Example.xlsx it gives only 0, and we have exactly same sheets. This is so frustrating hahah

I did, nothing happens. What is supposed to happen with Ctrl+Shift+Enter?

Yep the curly brackets appear but it still shows 0 :/

Hi! It works! The problem was that the business system exports numbers with "." is there a way to automaticly convert them on the way to the desired sheet(Sheet1)?

Or to make excel read the Book1 without "."

Yeah exactly thats how I do it manually. I thought about recording a macro to go to the workbook and remove all the "." on the document that gets exported from the business system.

Hi Bo_Ry thank you for all help. Could you please help me with one last thing. When the cost has been booked the date goes to column T. Is there a way to be able to catch this data too? Thank you again!

Thank you soooo much!!

hi
do you mean referencing a whole column in excel?

Hello Bo_RY,

Could you help me display dates in row 29 as the "Desired Results" Sheet in Example excel document. In case of two dates fall in same month, is it possible to display them in one cell?

I have tried figuring it out with your formula but with S column without any results

Thank you again!!

Hi Bo_Ry,
This isnt working due to I have different format on dates. It just returns strange dates. Is it any way we could make this universal so that it works with any computer that opens this docoment? Like we did before?

Hi Bory! Works so good, fixed my language settings!

The only problem I have right now is that it doesnt pick up the dates from column T in Book1. Is it possible for it to pick column T if column S doesnt have a date? And if neither of them have a date, it just dont show anything just like now? Thank you again!

I tried
"=TEXTJOIN(", ";;IF(TEXT(N$25;"MÅÅ")=TEXT(PLR!$S$13:$T$100;"MÅÅ");TEXT(PLR!$S$13:$T$100;"MMM-DD");""))"
But I got #Value error because it is trying to pick up the total sum in row 25 too...

Thanks bro works like a charm!!

Hi again Bo_Ry!

Could you help me make make a formula that picks column E minus columns G in "Book1" and distribute the answer equally in "Desired Results" from the date in F12 in "Desired Results" to the date N3 in "Book1". See the attached Example for more precise explenation.

Thank you again!

Hi Bo_Ry,

I'm trying to develop the formula and I can't seem to get it to work.

I want it to do the same, but the dates sometimes vary. I want it to sum for the actual month and catch the varying dates and distribute correctly. It only distributes to the date in N3. Is it possible to make it distribute for all dates in Book1 N and sum it for each month in Desired Results horizontially.

Ctrl+shift+enter:
=SUM(IF((E$25>=$F$12)*(Book1!$B$3:$B$10="06")*(E$25<=INDEX(Book1!$N$3:$N$10;LEFT(Book1!$C$3:$C$10;7)=$C29));INDEX(Book1!$E$3:$E$10-Book1!$G$3:$G$10;LEFT(Book1!$C$3:$C$10;7)=$C29)/(DATEDIF($F$12;INDEX(Book1!$N$3:$N$10;LEFT(Book1!$C$3:$C$10;7)=$C29);"m")+1);""))

Thank you again Bo_Ry!