Make a more complex VLOOKUP formula

Status
Not open for further replies.

lababa

New Member
Joined
Nov 26, 2018
Messages
7
Hi,
I have twoexcel documents, "Book1" and "Book2". Book1 is exportedfrom our business system. Book2 is a compilation of forecast from the businesssystem data that were exported.

Today Ihave to manually put in the data from Book1 to Book2. I want to automate thatprocess.
In Book2 Ihave dates in row 25 like dec-18, jan-19, feb-19 and so on. I want these datesto be matched with the dates in Book1 that are displayed as 2019-01-05 and soon, which are displayed vertically in column S. If a match is detected I want Book2 to take the data in column R inBook1 associated with the same row of the date and place it in Book2 in thesame date.

Now I’m using a rangefor the Book1 costs and dates and use VLOOKUP for the cells in BOOK2.

Problem number 1: Is thatwhen we export the information from our business system, the cost is in the leftcolumn of the dates. VLOOKUP is trying to match the dates of BOOK2 with thecost of column R. How do I manage to change it without changing the original format?

Problem number 2: Isthat VLOOKUP is matching the dates exactly, I want it to match only the months.
Problem number 3: Iwant it to sum the costs of the same months and display it as one cost. So forexample January has 2 costs, I want it to display the sum of these costs inBook2 jan-19.
My question is: is itpossible?

Formula I'm using now is which isn't working:
Code:

Book1: https://framapic.org/EdCfiEnwNE3G/xi0yGTaWM33i.PNG
Book2: https://framapic.org/3RciFks0qJBE/s3t48R3Ncna9.PNG
Desired results in Book2: https://framapic.org/uRdrUFL3gfWc/6OgCADbuL0iP.PNG

Also if someonehas a better solution than VLOOKUP, comment it below :D
Thank you in Advance
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Status
Not open for further replies.

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top