Vlookup formula with multi column

Hi dear,
hank you so much for your Help,
you are great and they are very valuable,
but I have a problem with the above case and can not solve it I ask for help,
From book 1 Column B. based on the date, the data from column C,I,O to transferred automatically to book 2 in column C with vlookup ore other method,
thank uoy do much,
Attached my book

Nice solution John, better than what I had. It can be just SUM instead of SUMPRODUCT though.

@toni I notice that 30/03/21 is repeated in Book-1, the second one is presumably meant to be 31/03/21.

Hello,
thank you very much john, your formula has helped me a lot, in the easy solution of the problem.
with respect.

If you have the same format for the year (i.e. each month uses 6 columns in the same order), you could try something like:

=INDEX('Book-1'!$C$5:$BT$35,MATCH(DAY(B3),DAY('Book-1'!$B$5:$B$35),0),COLUMN(INDEX($1:$1,1+6*(MONTH(B3)-1))))

Hi to all!

Another option could be:

PHP Code: 

=SUMIF('Book-1'!B$5:N$35,B3,'Book-1'!C$5:O$35) 


Blessings!

Well, it worked for me

John's should work if you extend the ranges? e.g. change $N to $BP and $O to $BQ

Edit - just seen your new attachment. Are you wedded to having that layour of months? It would be a lot easier if they were all in a line.

toni.1 ... just extend ranges to your next months with the same logic. If you understand the logic, you can replicate the formula. Try it! Blessings!

not correct sum,
i correct all date but still to give othe vrong sum ,
pls can you check now,with correction month ,

toni, the dates are still wrong in some places. e.g. 1st October appears twice, once in the correct place and again at the end of September.

Nick.Williams,

the problem is because the formula calculate wrong columns not dates,
instead of leaving the column the sum is leaving the total column.
if the date paste in other table formula not working, for example in 1 april not work,

If you are referring to my formula, the latest one does not work, it looks like I didn't paste the final version, sorry. I will edit my post. The working version is:

=INDEX('Book-1'!$A$5:$R$140,MATCH(DAY(B3),DAY('Book-1'!$B$5:$B$35),0)+35*INT((MONTH(B3)-1)/3),COLUMN(INDEX($1:$1,MOD(3+6*(MONTH(B3)-1),18))))

But also JohnTopley and John Vergara's work. Please tell me which one is giving the wrong answer.

dear my friends,
all of the options are wrong you can check,
if the column of date is blank your formula the are give the sum, only john vergara no,
but if the date is for example 01.01.2020 one year leter the option of john vergara give the wrong sum!
i cant solve the problem because my table some time must be blank or must be holidays.
thanks for you,

Well to be fair you didn't mention that you wanted to deal with dates outside your lookup range...and you still haven't siad what you want as a result. I guess to return blank? Or 0?

It's not difficult to provide a solution, once we know the problem. For example mine can be tweaked to be:

=IFERROR(IF(YEAR(B3)=2022,INDEX('Book-1'!$A$5:$R$140,MATCH(DAY(B3),DAY('Book-1'!$B$5:$B$35),0)+35*INT((MONTH(B3)-1)/3),COLUMN(INDEX($1:$1,MOD(3+6*(MONTH(B3)-1),18)))),""),"")

Change the ""),"") at the end to 0),0) if you want it to be 0 rather than blank.

I'm sure you can make similar alterations to the other options.

My friend,
I really want, the data in book 1 specifically in Columns: C, I, O to be transferred to book 2 in column C based on the date of columns B in both books,

Blessings!